Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ruchirj07
Starting Member
37 Posts |
 Posted - 2007-10-22 : 07:26:04
|
| Can any1 let me know if deleted table hold the value deleted from table or not.Scenario is: I need to maintain the history of the table. Update on any record take place by deleting it first and then inserting whole new row. So i have written trigger to track it but it seems after the row is deleted, the data from deleted table is also lost :(Can any1 tell me the solution for the same. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-22 : 07:30:11
|
| Yes. Update is a delete followed by the insert. So Deleted table contains old copy of the data while Inserted table contains new data, for UPDATE action. Show us your code, seems like something wrong with the code.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-22 : 08:13:58
|
| If your updates are actually done by a delete followed by an insert then an update trigger will never be fired.A delete trigger will have the old values in the deleted table and an empty inserted tableAn insert trigger will have the new values in the inserted table and an empty deleted tableAn insert + delete trigger will be fired twice - once for the insert, once for the delete with tables populated as above.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ruchirj07
Starting Member
37 Posts |
Posted - 2007-10-22 : 08:26:19
|
| My code deletes the record and then insert new record for updating the record and the trigger i'm using id "after insert".I hope this clears the doubt. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-22 : 08:41:57
|
Then nr's answer would clear your doubts.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-22 : 09:09:57
|
| An insert trigger will have the new values in the inserted table and an empty deleted table==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Triggers!!!