代码之家 › 专栏 › 技术社区 › stoic Kobus Kleynhans

使用触发器时检测更新了哪些列的最佳方法

audit triggers sql-server sql

2

stoic Kobus Kleynhans · 技术社区 · 15 年前

我有一个用户表,它有名字姓氏出生日期…以及其他几列。在那个特定的表上,我有一个更新触发器,如果有人更改了任何用户值,它将创建一个审计。

我的问题是,管理层希望通过以下方式显示一个可视审计:

DateChanged |Change                        |Details
--------------------------------------------------------------------------------
2010/01/01  |FirstName Changed             |FirstName Changed from "John" to "Joe"  
2010/01/01  |FirstName And Lastname Changed|FirstName Changed from "Smith" to "White "And Lastname changed from "els" to "brown"
2010/01/01  |User Deleted                  |"ark Anrdrews" was deleted

我认为解决这一问题的最佳方法是修改存储过程,但是如何指示触发器记录这些事情呢?

2 回复 | 直到 11 年前

1

5

sudhansu63 11 年前

在一个名为colums_updated()的触发器中可以调用一个函数。这将返回触发器表中列的位图,其中1表示表中的相应列已被修改。就其本身而言,这并不出色,因为您最终会得到一个列索引列表,并且仍然需要编写一个case语句来确定修改了哪些列。如果没有那么多列,那么可能没有那么糟糕,但是如果您想要更通用的列,那么您可以连接到information.columns,将这些列索引转换为列名。

下面是一个小示例,展示您可以做的事情:

CREATE TABLE test (
ID Int NOT null IDENTITY(1,1),
NAME VARCHAR(50),
Age INT
)

 GO
---------------------------------------
CREATE TRIGGER tr_test ON test FOR UPDATE 

AS

DECLARE @cols varbinary= COLUMNS_UPDATED()
DECLARE @altered TABLE(colid int)
DECLARE @index int=1

WHILE @cols>0 BEGIN
    IF @cols & 1 <> 0 INSERT @altered(colid) VALUES(@index)
    SET @index=@index+1
    SET @cols=@cols/2
END 

DECLARE @text varchar(MAX)=''
SELECT @text=@text+C.COLUMN_NAME+', '
FROM information_schema.COLUMNS C JOIN @altered A ON c.ordinal_position=A.colid
WHERE table_name='test'

PRINT @text

GO
------------------------------------------------------
INSERT test(NAME, age) VALUES('test',12)
UPDATE test SET age=15
UPDATE test SET NAME='fred'
UPDATE test SET age=18,NAME='bert'
DROP TABLE test

2

1

MatBailie 15 年前

MS SQL Server有一个函数updated()。它允许您指定一个字段名来判断是否有任何记录更改了该字段。

然而,我的经验是,所期望的功能常常超出了这种通用功能的限制。通常情况下,我只需将插入的伪表连接到删除的伪表,然后自己比较它们。

*这样的比较假设表具有唯一的标识,无论是单个字段还是某种类型的复合键。