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

Try something like this: CREATE TRIGGER YourTrigger ON YourTable AFTER INSERT,UPDATE,DELETE AS DECLARE @storyType char(1) --"I"=insert, "U"=update, "D"=delete SET @storyType=NULL IF EXISTS (SELECT * FROM INSERTED) BEGIN IF EXISTS (SELECT * FROM DELETED) BEGIN --UPDATE SET @storyType='U' END ELSE BEGIN --INSERT SET @storyType='I' END --handle insert or update data INSERT INTO YourLog (ActionType,ActionDate,.....) SELECT @GETDATE(),..... FROM INSERTED END ELSE IF EXISTS(SELECT * FROM DELETED) BEGIN --DELETE SET @storyType='D' --handle delete data, insert into both the history and the log tables INSERT INTO YourLog (ActionType,ActionDate,.....) SELECT @GETDATE(),..... FROM DELETED END --ELSE --BEGIN -- both INSERTED and DELETED are empty, no rows affected --END.

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 8 '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 8 '10 at 19:44 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 8 '10 at 20:02 excellent info. Having an update mixed with an insert was exactly what I was worried about.

– DefyGravity Dec 8 '10 at 20:41.

You need to associate (match) the rows in the inserted and deleted columns. Something like this should work better. Create trigger Worlds_After_IUD on update, delete as insert into HeloWorlds helloWorld.Id, helloWorld.

Text ... and more from inserted where myKeyColumn not in (select myKeyColumn from deleted) insert into HeloWorlds helloWorld. Id, helloWorld. Text ... and more from deleted where myKeyColumn not in (select myKeyColumn from inserted) insert into HeloWorlds 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 8 '10 at 19:38 see KM's comment on PK checking in this situation – DefyGravity Dec 8 '10 at 20:45.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.