如何在SQL Server触发器中复制插入，更新，删除的行 [英] How to copy an inserted,updated,deleted row in a SQL Server trigger(s)

问题描述

如果用户更改表 HelloWorlds ，那么我想要他们执行的操作，执行时间，然后将原始行的副本插入 HelloWorldsHistory 。

If a user changes table HelloWorlds, then I want 'action they did', time they did it, and a copy of the original row insert into HelloWorldsHistory.

由于列的长度，我宁愿避免使用单独的触发器来执行插入，更新和删除操作。

I would prefer to avoid a separate triggers for insert, update, and delete actions due to the column lengths.

我已经尝试过：

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个单独的触发器，尽管维护列列表并不是一件好事。

I've never seen an insert appear, but I've seen updates. I'm going to try 3 separate triggers, though maintaining the column lists will not be fun.

推荐答案

尝试如下操作：

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

这篇关于如何在SQL Server触发器中复制插入，更新，删除的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！