Insert a record that has been deleted

A trigger would be one way

Another way would be to put the delete into a stored proc
Prior to deleting the records run a query that selects the records that are about to be deleted and inserts them into the other table.

These two options are good.

Just make sure to use transactions to make sure that when one of the t-sql fails, do a rollback.

-- CK

Yes... if you use a stored procedure with multiple delete statements, use begin trans and commit trans to ensure the entire batch processes together.

DELETED URL

Quote: Originally Posted by dbpros Yes... if you use a stored procedure with multiple delete statements, use begin trans and commit trans to ensure the entire batch processes together. DELETED URL

dbpros, I know you want to be helpful but putting a base link to a website is not allowed, it is considered advertising spam. It is okay to link to a technical article as long as it won't be considered an advertisement. If you want to link to your own website you can PM the person and suggest to them to visit.

You're right, a trigger would be the best way to deal with this.

you will need to write an INSTEAD OF trigger to replace the default DELETE action. Inside the trigger, you will have access to the "deleted" object which will contain the deleted row(s) information. From there you can INSERT them into your new table.