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

发布于 2024-09-29 22:53:07 字数 605 浏览 2 评论 0原文

我有一个用户表，其中有|名字|姓氏|出生日期| ...以及其他一些专栏。在该特定表上，我有一个更新触发器，如果有人更改任何用户值，则会创建审核。

我的问题是，管理层希望以以下方式显示可视化审核：

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

我相信解决此问题的最佳方法是修改存储过程，但是我如何指示触发器记录这些事情？

原文

I have have a user table, that has |Firstname|Surname|DateOfBirth| ... and a few other columns. On that specific table i have a trigger on update that if someone changes any user values it creates an audit.

My problem is, management want's a visual audit displayd int the following way:

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

I beleive the best way to tackle this is by modifying the stored proc, but how can i instruct the trigger to record these kind of things?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

独自←快乐 2024-10-06 22:53:07

您可以在触发器中调用一个名为 COLUMS_UPDATED() 的函数。这将返回触发器表中列的位图，其中 1 表示表中相应的列已被修改。就其本身而言，这并不出色，因为您最终会得到一个列索引列表，并且仍然需要编写 CASE 语句来确定修改了哪些列。如果你没有很多列，可能还不错，但如果你想要更通用的东西，你也许可以加入 information_schema.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

There's a function you can call within a trigger called COLUMS_UPDATED(). This returns a bitmap of the columns in the trigger table, with a 1 indicating the corresponding column in the table was modified. On it's own that's not brilliant, as you'd end up with a list of column indices, and would still need to write a CASE statement to determing which columns were modified. If you've not got many columns that might not be so bad, but if you wanted something more generic you could perhaps join to information_schema.columns to translate those columns indices into column names

Here's a little sample to show the sort of thing you could do:

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

回复收藏 0 原文