Home SQL RowVersion incrementing before data has been augmented in Trigger
Reply: 0

SQL RowVersion incrementing before data has been augmented in Trigger

user1918
1#
user1918 Published in July 22, 2018, 6:16 pm

I've just started using T-SQL's RowVersion and I've noticed something very interesting. I created a Proc to update a table with a RowVersion column and at the end of this proc I print out a humanreadable date representation of RowVersion. I also added a Trigger which updates data in the table and prints out RowVersion before and after. It's given some interesting results...

From Insert Proc after data is inserted = 1900-01-01 05:43:13.373

From start of Trigger before we change any data = 1900-01-01 05:43:13.377

From end of trigger after data has changed = 1900-01-01 05:43:13.377

The start of the trigger value for the RowVersion column has changed from the end of the proc value for the RowVersion Column despite the fact we have not augmented any data yet. It's also the same as the end of the Trigger. Why is this? Is it something to do with the sequencing of SQL statement execution? Any help understanding this would be appreciated. The proc and trigger are listed below:

CREATE PROCEDURE [dbo].[TestRowVersionProc]
AS
    INSERT INTO TestRowversion (ID, Number)
    VALUES ('6', '6')

    DECLARE @rv DATETIME 
    SELECT @rv = GenerationNumber FROM TestRowversion WHERE ID = '6'
    SELECT CONVERT(VARCHAR(50), @rv , 121)
GO


CREATE TRIGGER [dbo].[TestRowversion_AfterInsertUpdate] ON [dbo].[TestRowversion] AFTER INSERT,UPDATE
AS
    if @@rowcount = 0 return
    set nocount on

    DECLARE @rv DATETIME 
    SELECT @rv = GenerationNumber FROM TestRowversion WHERE ID = '6'
    SELECT CONVERT(VARCHAR(50), @rv , 121)

    DECLARE @Id NVARCHAR(1) 
    SELECT @Id = ID FROM inserted

    UPDATE TestRowversion
    SET Number = 'FromTrigger'
    WHERE ID = '6'

    SELECT @rv = GenerationNumber FROM TestRowversion WHERE ID = '6'
    SELECT CONVERT(VARCHAR(50), @rv , 121)

GO
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.331841 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO