如何在 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.id, helloWorld.text ... and more from deleted 
    end
    else
    begin
insert into HelloWorldHistory (hwh_action, ..long column list..)
select 'DELETE', helloWorld.id, 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,ActionDate,.....)
        SELECT
            @HistoryType,GETDATE(),.....
            FROM DELETED

END
--ELSE
--BEGIN
--    both INSERTED and DELETED are empty, no rows affected
--END