Trigger Magic? ROWVERSION and LastUpdateDate Trigger

  • For reasons of "necessary complexity" we need to add a LastUpdateDate DATETIME2 column to a table that already has a ROWVERSION column on it.
    Additionally, we cannot use a DEFAULT value in the LastUpdateDate column as a large majority of the rows must remain NULL.  See "necessary complexity" comment...  :pinch: :blink: :pinch: Only set the value when new rows are added or existing rows are updated on a go-forward basis.

    CREATE TABLE dbo.test ( i int, v varchar(10), rv rowversion, LastUpdate datetime2 );
    To get the LastUpdateDate column to function properly we need to use a trigger to set the value as SYSUTCDATE().
    CREATE TRIGGER dbo.tr_test_iu ON dbo.test
    AFTER INSERT, UPDATE AS
    BEGIN
        UPDATE    t
        SET        t.LastUpdate = SYSUTCDATETIME()
        FROM    dbo.test AS t
        INNER JOIN Inserted AS i
        ON        t.i = i.i
        ;
    END
    ;
    GO

    The "problem" is that the ROWVERSION column gets updated a second time when the AFTER trigger fires.
    Any suggestions on how to get around that?  I was thinking it might be done with an INSTEAD OF trigger - just couldn't figure out how to get it to work.
    Do we just have to accept that either any insert/update will use two values of ROWVERSION or the insert/update routine must be modified to set the LastUpdate value at run time?
    I'm currently leaning toward pushing that the insert/update routing must be modified...

    Any thoughts, experience, trigger magic out there?
    Thanks!

  • I am not following the reasoning for the issue - if you add the new column with a default value the previous rows will be NULL and only new rows added will have the new value.  Defining the new column with a default value allows that default value to populate the new column when those rows are inserted.

    The rowversion column will be updated during a normal update operations - the LastUpdateDate will be inserted with the default value.

    Use tempdb

     Drop Table dbo.TestTable;
      Go

    Create Table dbo.TestTable (
       id int
      , dataValue varchar(20)
      , rv rowversion
       );
      Go

    Insert Into dbo.TestTable (id, dataValue) Values (1, 'one');
    Insert Into dbo.TestTable (id, dataValue) Values (2, 'two');
    Insert Into dbo.TestTable (id, dataValue) Values (3, 'three');
    Insert Into dbo.TestTable (id, dataValue) Values (4, 'four');
    Insert Into dbo.TestTable (id, dataValue) Values (5, 'five');

    Select *
     From dbo.TestTable;
      Go

    Alter Table dbo.TestTable Add LastUpdateDate datetime2 Default sysutcdatetime();
      Go

    Create Trigger dbo.tr_TestTable
      On dbo.TestTable
    After Update
      As
    Begin
    Update t
      Set t.LastUpdateDate = sysutcdatetime()
     From dbo.TestTable    t
    Inner Join inserted    i On t.Id = i.Id;
      End
      Go

    Insert Into dbo.TestTable (id, dataValue) Values (11, 'one');
    Insert Into dbo.TestTable (id, dataValue) Values (22, 'two');
    Insert Into dbo.TestTable (id, dataValue) Values (33, 'three');
    Insert Into dbo.TestTable (id, dataValue) Values (44, 'four');
    Insert Into dbo.TestTable (id, dataValue) Values (55, 'five');

    Select *
     From dbo.TestTable;
      Go

    Update dbo.TestTable
      Set dataValue = 'one updated'
    Where id In (1, 11);

    Select *
     From dbo.TestTable;
      Go
     

    The rowversion value may get updated twice - but since that value is only utilized to identify the row then that should not cause any problems.

    Am I missing something?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffery, thanks for the reply and check on my logic.
    Through some iterations I lost track and conflated NOT NULL and DEFAULT value that was in the original iteration.
    The minor issue or problem is that rowversion gets updated twice.  Big picture, not really an issue; just not "clean" coding.  Seems like it is just a trade off and some technical debt on how and where the logic will reside.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply