while 在触发器内循环以遍历 sql 中表的所有列
我在用户表上有一个像下面这样的触发器，用于插入到审计表中，其中列已更新和以前的值:
I have a trigger like below on user table to insert into the audit table with which column was updated and previous value:
ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[tbl_User]
AFTER UPDATE
AS
declare @fieldname varchar(128) ;
declare @OldValue varchar(255);
declare @CreateUser varchar(100) ;
declare @User_Key int;
select @CreateUser =i.user_name from deleted i;
SELECT @User_Key = i.user_key from inserted i;
if update(user_name)
begin
select @OldValue=j.user_name from deleted j;
set @fieldname = 'user_name';
insert into tbl_Audit(user_key, field_name, previuos_Value, user_name)
values(@User_Key ,@fieldname,@OldValue, @CreateUser);
end
但我的问题是我的桌子上有 100 个字段.我不能写 100 if 条件.我需要一个关于如何在其中使用 while 循环的建议，以及它将如何影响性能.
But my questions is I have like 100 fields on my table. I can't write 100 if conditions. And i need a suggestion how to use while loop in it, and how is it going to effect the performance.
谢谢
推荐答案
试试这个 -
ALTER TRIGGER [dbo].[trgAfterUpdate]
ON [dbo].[tbl_User]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @DocumentUID UNIQUEIDENTIFIER
DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
SELECT DocumentUID, ...
FROM INSERTED
OPEN cur
FETCH NEXT FROM cur INTO @DocumentUID, ...
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE
@BeforeChange XML
, @AfterChange XML
SELECT @BeforeChange = (
SELECT *
FROM DELETED
WHERE [DocumentUID] = @DocumentUID
FOR XML RAW, ROOT
)
, @AfterChange = (
SELECT *
FROM INSERTED
WHERE [DocumentUID] = @DocumentUID
FOR XML RAW, ROOT
)
INSERT INTO dbo.LogUser (DocumentUID, BeforeChange, AfterChange)
SELECT @DocumentUID, @BeforeChange, @AfterChange
-- your business logic
FETCH NEXT FROM cur INTO @DocumentUID, ...
END
CLOSE cur
DEALLOCATE cur
END
相关文章