January 2, 2019 at 2:51 pm
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!
January 2, 2019 at 3:45 pm
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
January 3, 2019 at 6:35 am
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