while循环在触发器内部循环以遍历sql中的表的所有列 [英] while loop inside a trigger to loop through all the columns of table in sql
本文介绍了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。我需要一个建议,如何在其中使用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
这篇关于while循环在触发器内部循环以遍历sql中的表的所有列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文