Showing posts with label situation. Show all posts
Showing posts with label situation. Show all posts

Monday, March 19, 2012

Conditional Trigger

I'm hoping someone here can help me with this situation...I've looked everywhere and cannot find a solution.

I have two separate databases. Table structures are different. A record is inserted into Table 1 of Database A. Then, when Field 1 in Table 1 is updated, I want my trigger to fire and insert certain values (selected into "inserted") to be inserted into Database B, Table 1.

I have created a trigger that will do just that. Here's my problem:

I need to ensure the trigger only fires if two conditions are met. If the value in Field 1 is updated to one specific value, and if the type of transaction (housed in a separate field) is one of several specific types (linked to the query from a separate table).

Otherwise, I want the trigger to be ignored completely.

Below is my current code. Can anyone here assist?
Thanks in advance for any help,
Dirk

CREATE TRIGGER [ELOGXWALK] ON [dbo].[Appointments]
AFTER UPDATE
AS

SET XACT_ABORT ON

IF UPDATE(Status)
BEGIN

declare @.recnum int
declare @.dateofenc datetime
declare @.timein varchar (5)
declare @.Code varchar (6)
declare @.Descr varchar (25)
declare @.clinic varchar (6)
declare @.status varchar (5)
declare @.Appointment_ID int
declare @.Encounter_Number int
declare @.nextnum int

select @.recnum = fldrec_num, @.dateofEnc = left(appointment_datetime, 11), @.timein=right('0'+convert(varchar,time_in), 4),
@.Code = Code, @.Descr = Descr, @.clinic = 'ONSITE', @.Status = status, @.Appointment_ID = Appointment_ID,
@.Encounter_Number = Encounter_Number from inserted
left join Crosswalk.dbo.EncounterLink on TypeID = Appointment_Type_ID
left join patients on patients.patient_id = inserted.patient_id
left join contacts on contacts.contact_id = patients.contact_id
left join DBOCC.MCH_OHM.XWALK.EMPLOYEE on FLDSSN = contacts.ssn
where Appointment_Type_ID in (select TypeID from Crosswalk.dbo.EncounterLink)
and status = 'A'

set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq
set fldnextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG') + 1
where fldtablename = 'ELOG'

insert into DBOCC.mch_ohm.XWALK.ELOG (FLDREC_NUM, FLDEMPLOYEE, FLDDATE, FLDTIME, FLDTYPE, FLDDESCR, FLDCLINIC, FLDRECNUM, FLDENCNUMBR)
values (@.nextnum, @.recnum, @.dateofenc, @.timein, @.code, @.Descr, @.clinic, @.appointment_id, @.Encounter_Number)

END

IF UPDATE (Encounter_Number)
BEGIN

select @.Appointment_ID = Appointment_ID, @.Encounter_Number = Encounter_Number from inserted
left join DBOCC.MCH_OHM.XWALK.ELOG on FLDRECNUM = inserted.Appointment_ID

update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = @.Encounter_Number
where FLDRECNUM = @.Appointment_ID

ENDcan you provide more specifics on the two conditions?|||First condition. The TypeID referenced in the script must be any one of about 10 different values. These are stored in a separate table (EncounterLink) along with the corresponding value I want to load into Database B. If the TypeID does not equal one of the values in the EncounterLink table, I want the trigger to disregard everything.

Second condition. The Status field should only fire the trigger if a certain value is entered. In this case, only the value "A" should fire the trigger. In the future I will probably want one or two different trigger functions based on different values.

Dirk|||great! your current design will work if one row is updated but will fail (in some fashion) if 2+ rows are updated. Do you want to handle multiple row updates? also, do you want the Encounter_number updated only if the TypeID and Status are okay or are these seperate events?|||I should not have to deal with multiple row updates, because the front end application will only do a single row at a time anyway (clicking a button in the app triggers the change).

I've actually made progress by specifically referencing "inserted" in the where statement of my select and adding an "If @.@.rowcount > 0" and "Begin" command just prior to the set statement. It seems to be working properly all around, but any streamlining suggestions will be more than welcome, as I am not really a DBA - my company justs asks that I play one during working hours!

Dirk|||it sounds like you are on your way...

I would have taken a diffrent approach so the trigger could handle multiple row updates but the bottom line is that you understand the trigger and it works. I would suggest two things though,

First
----------------------------
set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = @.nextnum + 1 where fldtablename = 'ELOG'

or even

begin transaction
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = fldnextnum + 1 where fldtablename = 'ELOG'
select @.nextnum = (fldnextnum - 1) from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG'
commit transaction

And
----------------------------
IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join DBOCC.MCH_OHM.XWALK.ELOG on inserted.Appointment_ID = DBOCC.MCH_OHM.XWALK.ELOG.FLDRECNUM
end
----------------------------

these are minor things but (IMHO) you should save every step you can when writting triggers.|||Thanks for the tips! Both make sense to me, but the second one does not work...

Server: Msg 117, Level 15, State 2, Procedure ELOGXWALK, Line 49
The number name 'DBOCC.MCH_OHM.XWALK.ELOG' contains more than the maximum number of prefixes. The maximum is 3.

Dirk|||ARG!!! I do that sometimes.
I don't use liked servers that much but I think is should be something like

IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join OPENQUERY(DBOCC, select FLDRECNUM from MCH_OHM.XWALK.ELOG) elog on inserted.Appointment_ID = elog.FLDRECNUM
end

What you have is just fine though.

Saturday, February 25, 2012

Conditional execution of first task

I have a situation where I'd like to conditionally execute the first task in a package based on the contents of a user variable.

If user variable "Var1" is false I want to begin execution with the first task.

If "Var1" is true I want to begin execution at the second task.

My first thought of course was SequenceContainer, but the same issue would exist for the first task in a SequenceContainer.

Is there a way to do this?

Thanks!

To conditionally execute a task you would use a constraint with an expression on it, but you need two tasks (containers) to do this. The ideal answer is to use something that does nothing, but offers a start point for the constraint. A sequence container works rather well, just drop it on and collpase it (the arrow on the right-hand side of the header). You can then link that to the real task, nothing should go inside that sequence container.|||

I have created a sample solution inline with Darren's suggeston to use 'Sequence Container' for placing the precedence condition @. http://mystutter.blogspot.com/2006/03/sql-server-integration-services.html

Please let me know your comments.

Thanks,
Loonysan

|||

Thank you. Your example was perfect.

I found another solution as well. I created a Sequence Container, inside that I created another Sequence Container and my first task. I put an expression constraint between those. Then I linked the outer Sequence Container to the second task.

I appreciate the help.

Tuesday, February 14, 2012

concatnate values for different rows

Hi ,

I have a situation where i need to concatnate values from different rows and store it a one string.

sample

dealid date

1 1/5/2007

1 2/4/2009

2 5/5/2004

2 8/5/2006

2 4/8/2006

so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string.

Is ther any way i could do it, Any suggestions appreaciated

Thanks

Ashsih

Maybe something like:

select distinct dealId,
reverse(substring(reverse(
( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable b
where a.dealId = b.dealId

order by date desc
for xml path('')
)), 3, 300))
from theTable a

|||

For a particular DealD:

declare @.targetDeal int set @.targetDeal = 1

select reverse(substring(reverse

( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable

where dealId = @.targetDeal

order by date desc
for xml path('')

), 3, 300)) as DealDates