How To Copy An Inserted,updated,deleted Row In A Sql Server Trigger(s)

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 separ

Solution 1:

try something like this:

CREATETRIGGER YourTrigger ON YourTable
   AFTER INSERT,UPDATE,DELETEASDECLARE@HistoryTypechar(1) --"I"=insert, "U"=update, "D"=deleteSET@HistoryType=NULL

IF EXISTS (SELECT*FROM INSERTED)
BEGIN
    IF EXISTS (SELECT*FROM DELETED)
    BEGIN--UPDATESET@HistoryType='U'ENDELSEBEGIN--INSERTSET@HistoryType='I'END--handle insert or update dataINSERTINTO YourLog
            (ActionType,ActionDate,.....)
        SELECT@HistoryType,GETDATE(),.....
            FROM INSERTED

ENDELSE IF EXISTS(SELECT*FROM DELETED)
BEGIN--DELETESET@HistoryType='D'--handle delete data, insert into both the history and the log tablesINSERTINTO YourLog
            (ActionType,ActionDate,.....)
        SELECT@HistoryType,GETDATE(),.....
            FROM DELETED

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

Solution 2:

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

createtrigger [HelloWorlds_After_IUD] on [HelloWorlds]
FORinsert, update, deleteasinsertinto HeloWorldsHistory
select'INSERT', helloWorld.id, helloWorld.text ... and more 
from inserted
where myKeyColumn notin (select myKeyColumn from deleted)

insertinto HeloWorldsHistory
select'DELETE', helloWorld.id, helloWorld.text ... and more 
from deleted
where myKeyColumn notin (select myKeyColumn from inserted)

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