I use an UpdateDT datetime field on all tables I want to track the last modification date. This field is updated by a trigger in my Sql 2000 database:
CREATE TRIGGER Trig_MyTable_Update ON dbo.MyTable FOR INSERT, UPDATE
AS
IF @@ROWCOUNT=0 RETURN
UPDATE U SET UpdateDT = GETDATE()
FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id
This looked to me as a simple way of accomplishing what I needed. However this trigger is source of deadlocks. I made a simple .NET application that start 2 threads that each repetedly update the same row in MyTable. It takes a few seconds and a deadlock appears.
My understanding is:
- Thread-1: Call Update on a row.
- Thread-2: Call Update on same row, but waits that Thread-1 finishes.
- Thread-1: Trigger gets called to update same row, waits that Thread-2 finishes.
- Sql Server detects deadlock and terminate one of the queries (let say Thread-2).
- Thread-2: Aborts and raise an error.
- Thread-1: Completes trigger Update statement.
- Thread-1: Commits main Update and terminates.
I successfully solved this issue by using a pair of INSTEAD OF triggers like this (only UPDATE shown below):
CREATE TRIGGER Trig_MyTable_Update ON dbo.MyTable INSTEAD OF UPDATE
AS
IF @@ROWCOUNT=0 RETURN
UPDATE U SET
U.Field1 = I.Field1,
U.Field2 = I.Field2,
U.Field3 = I.Field3,
U.Field4 = I.Field4,
U.Field5 = I.Field5,
U.Field6 = I.Field6,
U.Field7 = I.Field7,
U.Field8 = I.Field8,
U.Field9 = I.Field9,
U.UpdateDT = GETDATE()
FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id
But this becomes just too complicated for a simple timestamping thing:
- Other (AFTER) triggers will think that all columns are updated when querying the UPDATED(field) function.
- If we change the table structure, we must not forget to update the INSTEAD OF trigger which complicates maintenance.
Is there something I missed?
Thanks,
J-F