我在用户表上有一个类似下面的触发器,用于插入审计表,更新了哪个列和之前的值：
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循环的建议,以及它将如何影响性能.
谢谢
解决方法
试试这个 –
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
