how to update column on same row with trigger without recursion? 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?
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:

IF (TRIGGER_NESTLEVEL() &gt; 1)
BEGIN
  -- Uncomment the following PRINT line for debugging
  -- PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;


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 the context_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:

IF (CONTEXT_INFO() = 0x01)
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;
ELSE
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Initial call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  SET CONTEXT_INFO 0x01;
END;


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 the UPDATE(column_name) function can only tell you if the column was in the SET 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 the LastUpdated column has the actual date &amp; time of the modification):

UPDATE ct
SET    ct.LastUpdated = ct.LastUpdated
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = '1900-04-01`
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = 1
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = GETDATE() + 90
FROM   Contact ct
WHERE  ...


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 an INSERT from another trigger does not adversely affect it:

TRIGGER_NESTLEVEL( @@PROCID , 'AFTER' , 'DML' )