On Delete Trigger Help

[GDX]

I am trying to create a simple trigger to backup the data before its being deleted from the authors table but i am having some problems doing that:

CREATE TRIGGER [AUTHORDEL] ON [dbo].[authors]
FOR DELETE
AS

INSERT INTO author_delete ( select * from deleted where au_id=@@identity )

Anyone has any idea what I may be doing wrong?

Thank you!

Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[vongrunt]

@@identity makes no sense here... use only:

INSERT INTO author_delete  
   select * from deleted

This assumes that tables "authors" and "author_delete" have absolutely identical structure.

 

[SQLSister]

Vongrunt is correct about what to do in the trigger. @@identity would not have meaning here. The deleted pseudotable is only available within a trigger and contains all the records that were deleted. (There is another psuedotable called inserted for all inserted records; for usdates both tables are used, deleted for the old data and inserted for the new.)

GDX, just in case you are doing this elsewhere, do not ever use @@identity. If you have a trigger on the table which inserts into another table with an identity column, this is the ID it will return not the one from the original table. @@identity is very bad for data integrity. Where appropriate in your stored procedures use scope_identity() instead.

Questions about posting. See faq183-874