By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,377 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Insert a record that has been deleted

P: 90
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
Jun 18 '08 #1
Share this Question
Share on Google+
5 Replies


Delerna
Expert 100+
P: 1,134
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.
Jun 19 '08 #2

ck9663
Expert 2.5K+
P: 2,878
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
Jun 19 '08 #3

P: 15
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
Jun 23 '08 #4

RedSon
Expert 5K+
P: 5,000
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.
Jun 24 '08 #5

Expert 100+
P: 145
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.
Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER [dbo].[trg_MyDeleteTrigger] 
  2.    ON  [dbo].[tbl_myTable] 
  3.    AFTER DELETE
  4. AS 
  5. BEGIN
  6.  
  7.      INSERT INTO myNewTable 
  8.      (Field1,
  9.       Field2,
  10.       Field3)
  11.       VALUES
  12.        (inserted.ID,
  13.         inserted.Title,
  14.         inserted.Code);
  15.  
  16. END
Jun 30 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.