how to update column on same row with trigger without recursion?

6 votes

2 answers

19636 views

sql-server sql-server-2012 trigger deadlock row-modification-time

                          SQL Server 2012.

I need to update column [LastUpdated] with the current date and time whenever a record changes in my table.  Currently I have:

    CREATE TRIGGER Trig_LastUpdated ON Contact AFTER UPDATE
    AS
    SET NOCOUNT ON

    UPDATE ct
    SET LastUpdated = GETDATE()
    FROM Contact ct
    INNER JOIN Inserted i
	    ON ct.IDContact = i.IDContact

But this is recursive and I don't want that, causes deadlocks and other weirdness.  I cannot turn off recursive triggers globally.  I see that INSTEAD OF triggers are non-recursive, but if I do that do I have to check every other column in the Inserted to see if it got updated or will SQL Server handle that for me?  What's the best way to do this?
                        

Asked by Zaphodb2002 (399 rep)

Oct 28, 2016, 02:38 PM
Last activity: Aug 21, 2017, 04:44 AM

how to update column on same row with trigger without recursion?

Related Questions