Insert a record that has been deleted

[raghulvarma]

I need to insert the records in table2 that have been deleted in table1 how should I proceed? Will trigger help me out?
If so some one plz help how to do that

[Delerna]

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.

[ck9663]

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

[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

[RedSon]

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.

[Jerry Winston]

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.

CREATE TRIGGER [dbo].[trg_MyDeleteTrigger] 
   ON  [dbo].[tbl_myTable] 
   AFTER DELETE
AS 
BEGIN

     INSERT INTO myNewTable 
     (Field1,
      Field2,
      Field3)
      VALUES
       (inserted.ID,
        inserted.Title,
        inserted.Code);

END