How to copy an inserted,updated,deleted row in a SQL Server trigger(s)

Question

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.

I've tried this:

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

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.

don't use DELETED as the source of data for UPDATEs, you'll just be logging the previous value which was already recorded. Your log row will be "Update" type but the data could have been from an insert. See me comment in my answer. — KM., Dec 08 '10 at 19:59

score 23 · Accepted Answer · answered Dec 08 '10 at 19:27

23

try something like this:

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

answered Dec 08 '10 at 19:27

KM.

101,727
34
178
212

works great. Being close made me feel much better about digging up my dusty MS SQL skills after being in Oracle for the past few years. – DefyGravity Dec 08 '10 at 19:34
My end solution will be this "architecture", with bob's pk columns check between the source table and the temporary transaction trigger tables. Also, i'll be pulling the 'UPDATE' data from the deleted table, as opposed to inserted. – DefyGravity Dec 08 '10 at 19:44
4

I would strongly recommend that you log the UPDATE from the INSERTED table. Here is an example: insert row data=AAA (INSERTED=AAA goes to log), update same row to BBB (INSERTED=BBB, DELETED=AAA, I say send BBB to log, and not AAA, that was already recorded). Also, when using the `IF EXISTS`, all the "key checks" are not necessary and just overhead. A trigger can only fire for 1 command at a time, an INSERT or an UPDATE or a DELETE, so there is no need to match keys up to determine operation. You'll never have a UPDATEs data mixed with an INSERTs data within a trigger. – KM. Dec 08 '10 at 20:02
excellent info. having an update mixed with an insert was exactly what I was worried about. – DefyGravity Dec 08 '10 at 20:41
2

@KM This is quite elegant for its simplicity and for the insight into the atomicity of the three statements eliminating the need for PK matching. I even tested this with the 'MERGE' statement which would appear to mix operations in one batch, but in reality it does not, and this logic holds well. – mdisibio Jun 20 '13 at 21:18

score 7 · Answer 2 · answered Dec 08 '10 at 19:30

You need to associate (match) the rows in the inserted and deleted columns. Something like this should work better.

create trigger [HelloWorlds_After_IUD] on [HelloWorlds]
FOR insert, update, delete
as

insert into HeloWorldsHistory
select 'INSERT', helloWorld.id, helloWorld.text ... and more 
from inserted
where myKeyColumn not in (select myKeyColumn from deleted)

insert into HeloWorldsHistory
select 'DELETE', helloWorld.id, helloWorld.text ... and more 
from deleted
where myKeyColumn not in (select myKeyColumn from inserted)

insert into HeloWorldsHistory
select 'UPDATE', helloWorld.id, helloWorld.text ... and more 
from inserted
where myKeyColumn in (select myKeyColumn from deleted)

I am definitely going to match on the pk! thanks for that catch! — DefyGravity, Dec 08 '10 at 19:38

How to copy an inserted,updated,deleted row in a SQL Server trigger(s)

2 Answers2

Linked