在SQL Server中是否有相当于rowversion的表格? [英] Is there an equivalent of rowversion in SQL Server, but for a table?
问题描述
我正在寻找类似于 rowversion ,但对于整个表格:一个自动生成的唯一编号,可用于了解表格是否已更改。
例如:
$ INSERT INTO FOO(...)VALUES( 1,'Hello world')
SELECT TABLE_VERSION('FOO')
0x00000000000007D5
它是否存在于SQL Server中?
如果没有,是否有其他选择?允许跟踪列变化的东西也会很棒。
我一直在考虑使用 HASHBYTES ,但不幸的是,它只给出一个列值而不是整列的散列。
在SQL Server中不是这样的事情,但是你可以用触发器轻松实现它。我已经使用它来协助缓存Web应用的失效。
首先创建表etags:
CREATE TABLE etags
(
etag_name varchar(128)NOT NULL PRIMARY KEY,
etag_value uniqueidentifier NOT NULL
)
$ c为 Foo
表插入一个值:
> INSERT INTO etags VALUES('foo',NEWID())
然后为 foo
表创建一个触发器,该表在表更改时更新etag值
< $ p $ CREATE TRIGGER foo_etag
ON foo FOR INSERT,UPDATE,DELETE
AS
SET NOCOUNT ON
UPDATE etags SET etag_value = NEWID()
WHERE etag_name ='Foo'
I am looking for something similar to rowversion but for the whole table : a unique number automatically generated that can be used to know if table has changed.
Eg :
SELECT TABLE_VERSION('FOO')
0x00000000000007D2
INSERT INTO FOO (...) VALUES (1, 'Hello world')
SELECT TABLE_VERSION('FOO')
0x00000000000007D5
Does it exists in SQL Server ?
If not, is there any alternative ? Somethings that allow to track changes on a column would be great too. I have been thinking of using HASHBYTES, but unfortunately it only gives hash for a single column value not for the whole column.
There is not such a thing in SQL Server, however you can implement it easly with triggers. I have used it to assist cache invalidation for web apps.
first create the table etags:
CREATE TABLE etags
(
etag_name varchar(128) NOT NULL PRIMARY KEY,
etag_value uniqueidentifier NOT NULL
)
Insert a value for the Foo
table:
INSERT INTO etags VALUES ('foo', NEWID())
Then create a trigger for the foo
table that updates the etag value when table changes
CREATE TRIGGER foo_etag
ON foo FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
UPDATE etags SET etag_value = NEWID()
WHERE etag_name = 'Foo'
这篇关于在SQL Server中是否有相当于rowversion的表格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!