Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Posts
    36

    Unanswered: 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

    END

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    can you provide more specifics on the two conditions?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2002
    Posts
    36
    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

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jun 2002
    Posts
    36
    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

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Last edited by Paul Young; 06-18-02 at 11:49.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jun 2002
    Posts
    36
    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

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •