Share via

T-SQL: Find Row Updated by Another User

  • Article

One of my colleagues asked about an interesting problem:

In a table, the DBA wants to see every change in this table (for example, inserting or updating rows) by other users.

The solution has SQL SERVER as we must use "TIMESTAMP" function.

What is a "TIMESTAMP" function? Is this is time function from SQL server?

Answer – no, this is no time function.

A timestamp is a special binary(8) value that's guaranteed to be unique across a database and given value from @@DBTS system function and from different databases give us different value.

For example:

USE tempdb
 
select @@DBTS as tempdb_timestamp
 
use master  
 
select @@DBTS as masterdb_timestamp
 
use msdb  
 
select @@DBTS as msddbdb_timestamp
 
 
tempdb_timestamp
0x0000000000000887
 
 
masterdb_timestamp
0x0000000000000FA0
 
 
msddbdb_timestamp
0x0000000000002710

Using "TIMESTAMP" function with inserting time in different rows will give us a  different value.

For example:

CREATE TABLE  #test(k1 int  identity,timestamp timestamp)
 
INSERT #test DEFAULT VALUES
go 5
 
select * from #test
 
drop table  #test
 
 
k1  timestamp
1   0x0000000000000888
2   0x0000000000000889
3   0x000000000000088A
4   0x000000000000088B
5   0x000000000000088C

Iin this example rows of columns, timestamp was give in inserting time different value from "TIMESTAMP" function and in updating time default value from  @@DBTS was given different value from updating process.

To find different value we need "TSEQUAL()" function SQL SERVER.

This command compares two timestamp values—returns 1 if they're identical, raises an error if they're not.

Then we can create our query for a given message from updating our table by another user.

CREATE TABLE  #test
(k1 int  identity,
timestamp timestamp)
DECLARE @ts1 timestamp, @ts2 timestamp
SELECT @ts1=@@DBTS, @ts2=@ts1
SELECT CASE  WHEN TSEQUAL(@ts1, @ts2) THEN 'Equal'  ELSE 'Not Equal'  END
INSERT #test DEFAULT VALUES
SET @ts2=@@DBTS
SELECT CASE  WHEN TSEQUAL(@ts1, @ts2) THEN 'Equal'  ELSE 'Not Equal'  END
GO
 
 
 
(1 row(s) affected)
 
(1 row(s) affected)
Msg 532, Level  16, State 2, Line 11
The timestamp  (changed to  0x0000000000000892) shows that the row has been updated by another user.

The  result was successful from inserting process  we got message "The row has been updated ``by another ``user".