sql-server – 如何在SQL Server触发器中复制插入的,更新的,已删除的行

如果用户更改了表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

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!