locked
Two Update triggers on the same table RRS feed

  • Question

  • Hi,

    My question is as follows: right now I have two triggers on my table that fires after each update. One of them updates the "ModifiedDate" column on my main table, the other copies the row into my history table. The problem is that the history trigger is firing twice, once on my initial update, and once more after my modifieddate trigger. Is there a way for to make the history trigger fire only once?

    The code:

    CREATE TRIGGER tr_cdc_ModifiedDate on dbo.CDC FOR UPDATE AS
        UPDATE CDC
        SET ModifiedDate=GETDATE()
        FROM CDC INNER JOIN inserted i ON CdC.Key = i.Key
    GO

     

    CREATE TRIGGER tr_cdc_Update ON dbo.CDC FOR UPDATE AS INSERT INTO cdc_history SELECT * ,''U'' FROM Inserted

     

    I already tried setting the trigger priority with

    exec sp_settriggerorder 'tr_CDC_ModifiedDate', 'first', 'update', null
    exec sp_settriggerorder 'tr_CDC_Update', 'last', 'update', null

    Thanks

    Thursday, March 3, 2011 1:30 AM

Answers

  • I just combined the two triggers. But I will try some of the other ideas when I have time. Thanks :)
    • Marked as answer by PursuitOfGold Thursday, March 3, 2011 5:56 PM
    Thursday, March 3, 2011 5:56 PM

All replies

  • There was a very similar thread recently which may help.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 3, 2011 1:43 AM
  • Have you looked at third party tools for audits? They will not slow down your application, and they meet legal tests.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, March 3, 2011 3:42 AM
  • You could create an INSTEAD of UPDATE trigger, which would alter the modified date before inserting all the records. 

    ALTER TRIGGER tr_cdc_ModifiedDate ON dbo.CDC
    INSTEAD OF UPDATE
    AS

    INSERT INTO CDC
    (
    --...ALL CDC columns EXCEPT ModifiedDate,
    ModifiedDate
    )
    SELECT
    --...ALL INSERTED CDC columns EXCEPT ModifiedDate,
    ModifiedDate=GETDATE()
    FROM
    INSERTED
    WHERE INSERTED.KEY = CDC.Key

    You would want to make sure that when you do a partial insert/update that Inserted table included all the columns in that table.  My recollection is that it wouldn't, which would mean my logic above would not work.  If my hunch is right, you would need to find the updated rows from CDC and put them in a temporary table, then update those rows from INSERTED and modify the ModifiedDate then then apply the data from the temporary table to the CDC table.

    But it can be done with an INSTEAD OF trigger.

    -Eric Isaacs
    Thursday, March 3, 2011 4:33 AM
  • I just combined the two triggers. But I will try some of the other ideas when I have time. Thanks :)
    • Marked as answer by PursuitOfGold Thursday, March 3, 2011 5:56 PM
    Thursday, March 3, 2011 5:56 PM