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?
Best Answer
Given that you cannot disable recursive triggers, the next best options are:
Have the trigger detect how many levels deep it is using TRIGGER_NESTLEVEL function. Use this at the beginning of the trigger to simply exit if it is not the 1st trigger execution in the stack. Something along the lines of:
This will require a little bit of testing to see how it is affected by the initial insert being done by another trigger (in case that ever becomes an issue, but it might not). If it doesn't work as expected when called by another trigger, then try setting some of the parameters to this function. Please see the documentation (linked above) for details.
Set a flag in the session-based "context info" using SET CONTEXT_INFO. Context info is a
VARBINARY(128)
value that exists at the session level and retains its value until overwritten or until the session ends. The value can be retrieved either by using the CONTEXT_INFO function or selecting thecontext_info
column from either of the following DMVs: sys.dm_exec_requests and sys.dm_exec_sessions.You could place the following at the beginning of the trigger:
This option doesn't work so well if you are already using Context Info for some other reason. But, anyone using SQL Server 2016 can make use of SESSION_CONTEXT, which is a new session-based set of key-value pairs.
Either of those methods is more reliable than using
IF NOT UPDATE(LastUpdated)
since theUPDATE(column_name)
function can only tell you if the column was in theSET
clause or not. It cannot tell you if the value has changed, or if it changed to the "current"GETDATE()
value that you are expecting / wanting. Meaning, all of the following statements bypass the desired effect of the trigger (i.e. making sure that theLastUpdated
column has the actual date & time of the modification):The safest method is probably using
TRIGGER_NESTLEVEL()
(option 1) and passing in the parameters for checking just this particular trigger, so that being called due to anINSERT
from another trigger does not adversely affect it: