Two Update triggers on the same table

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

I just combined the two triggers. But I will try some of the other ideas when I have time. Thanks :)