5

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?

3

Given that you cannot disable recursive triggers, the next best options are:

  1. 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() > 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.

  2. 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 & 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' )
1

As @ypercubeᵀᴹ pointed out in the comments, How do I add a “last updated” column in a SQL Server 2008 R2 table? has the answer. Checking to see if the column has been updated just skips the logic and works the way I expect it to. When the user updates the column manually in takes their update, and doesn't cause recursion when they don't. For some reason I thought it would stop the update entirely but that's obviously not the case.

  • Please be aware of the risky assumption being made when reasoning that "When the user updates the column manually it takes their update". While that is a true statement, it ignores the fact that "their update" that is being honored is not, in any way, required to be the particular update you are wanting. Please see my answer for explanation as well as info on how to accomplish what you are wanting to do :-). – Solomon Rutzky Dec 21 '16 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.