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.
Showing posts with label everywhere. Show all posts
Showing posts with label everywhere. Show all posts
Monday, March 19, 2012
Conditional Trigger
Saturday, February 25, 2012
Conditional execution of DTS package task
I've searched everywhere for this but can't find the answer
I want to run a DTS package that simply executes a SQL statement to get
a count of rows in a table, if the resulting number is greater than
zero I want to execute another step in the same package, else just
quit.
I don't to pass global vars from one package to another
How can I do this?
Any help would be greatHi Scouser,
I've got a suggestion: Create a stored proc that does the count of the
records and then execute the DTS from the procedure, if the condition
is true.
Look at this message if you want to see how to execute the DTS-Package
from a stored procedure:
http://groups.google.de/group/borla...62580101f3 8fe
Michael
www.zankl-it.de
Subscribe to:
Posts (Atom)