如果用户更改了表HelloWorlds,那么我想要’他们做的动作’,他们做的时间,以及原始行插入HelloWorldsHistory的副本.
由于列长度,我宁愿避免单独的插入,更新和删除操作触发器.
我试过这个：
create trigger [HelloWorlds_After_IUD] on [HelloWorlds]
FOR insert,update,delete
as
if @@rowcount = 0
return
if exists (select 1 from inserted) and not exists (select 1 from deleted)
begin
insert into HelloWorldHistory (hwh_action,..long column list..)
select 'INSERT',helloWorld.id,helloWorld.text ... and more from inserted
end
else
if exists (select 1 from inserted) and exists (select 1 from deleted)
begin
insert into HelloWorldHistory (hwh_action,..long column list..)
select 'UPDATE',helloWorld.text ... and more from deleted
end
else
begin
insert into HelloWorldHistory (hwh_action,..long column list..)
select 'DELETE',helloWorld.text ... and more from deleted
end
end
我从未见过插页出现,但我看到过更新.我将尝试3个单独的触发器,但保持列列表不会很有趣.
解决方法
尝试这样的事情：
CREATE TRIGGER YourTrigger ON YourTable
AFTER INSERT,UPDATE,DELETE
AS
DECLARE @HistoryType char(1) --"I"=insert,"U"=update,"D"=delete
SET @HistoryType=NULL
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
IF EXISTS (SELECT * FROM DELETED)
BEGIN
--UPDATE
SET @HistoryType='U'
END
ELSE
BEGIN
--INSERT
SET @HistoryType='I'
END
--handle insert or update data
INSERT INTO YourLog
(ActionType,ActionDate,.....)
SELECT
@HistoryType,GETDATE(),.....
FROM INSERTED
END
ELSE IF EXISTS(SELECT * FROM DELETED)
BEGIN
--DELETE
SET @HistoryType='D'
--handle delete data,insert into both the history and the log tables
INSERT INTO YourLog
(ActionType,.....
FROM DELETED
END
--ELSE
--BEGIN
-- both INSERTED and DELETED are empty,no rows affected
--END
