使用触发器保存SQL Server 2008中的更新列
我在数据库中有一个大表,我想跟踪对各个记录所做的更改。更确切地说,我想记录日期和对列进行的更改。使用触发器保存SQL Server 2008中的更新列
由于表格中有25个以上的列,我不想单独测试它们。
日志记录表看起来像ID-Date-Table-Column-OldValue-NewValue
在我AFTER UPDATE
触发我想检查哪些列有不同的值,并将其登录到我的表。
我知道我可以得到表的列有:
DECLARE @meta_table TABLE (
idx smallint Primary Key IDENTITY(1,1)
, TABLE_NAME nchar(100), COLUMN_NAME nchar(100), COLUMN_ID int
)
INSERT @meta_table
SELECT TABLE_NAME, COLUMN_NAME,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM MYDATABASE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable';
我可以遍历列:
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @meta_table)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM @meta_table))
BEGIN
SET @col = (SELECT COLUMN_NAME FROM @meta_table WHERE idx = @i)
-- do something with @col
SET @i = @i + 1
END
在第一个步骤,我想检查所有列,但像这样的东西不起作用
IF (SELECT @col FROM inserted) <> (SELECT @col FROM deleted)
BEGIN
-- INSERT into logging table ...
END
此外,这只会检查第一行的更新,所以我woul d需要为删除/插入表中的每一行执行此操作。
嗯 - 如果您只想记录已更改的字段,那么您肯定需要分别检查每个字段。但是,正如您发现的那样,比较在不同的数据类型上工作不一样。所以你可能需要检查每个字段的数据类型,并做相应的比较。我相信这是一个名为INFORMATION_SCHEMA“DATA_TYPE”字段,所以当你做你的元表插入,添加其他列:
INSERT @meta_table
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM MYDATABASE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable';
,然后在循环检查它:
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @meta_table)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM @meta_table))
BEGIN
SELECT @col = COLUMN_NAME, @dataType = DATA_TYPE
FROM @meta_table WHERE idx = @i)
-- do something with @col
SET @i = @i + 1
END
然后你有*检查数据类型,你做比较之前..
IF(@dataType = 'varchar')
BEGIN
-- do your comparison and insert here
END
ELSE IF(@dataType = 'int')
BEGIN
-- do you comparison and insert here
END
....
...等等,所有不同的数据类型,你在表中有...
嗯 - 所以我想你也想这样做多行,在一个更新语句修改多行的情况下 - 在这种情况下,你需要光标选择所有修改后的行,然后检查每一行.... –
迭代表格列没有意义。由于触发器是针对特定的表格,因此您已经知道它具有哪些列!用你感兴趣的列简单地编写触发器。你说这可能是一个重复的,无聊的,容易出错的任务?好的程序员在这种情况下自动执行任务,例如。通过代码生成自动生成触发器,瞬间重构它们并使其与模式更改保持同步。部署运行时模式发现不是要走的路。
这些列不在我的控制之下,可以随时添加/删除,所以我必须在运行时获取列。 – Darcara
你看过COLUMNS_UPDATED()吗? – HABO