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 |
|
Kalpa
Starting Member
9 Posts |
 Posted - 2008-07-30 : 03:12:51
|
| i have got two tables "ProcessDaily" and "ProcessHistory",now i have written a procedure to insert a particular row from Process daily to process History table.now after insertion i need to delete the inserted row..I need to delete only when the insertion is sucessfull..I need a condition as such..if the transaction is sucessfull only then the deletion will take place....please suggest me the waythank you |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-07-30 : 04:05:27
|
| Wrap it in a transaction. That's what they are for.If you are concerned about deleting different rows to the ones you inserted, then join the history ones back to the daily ones to do the delete. Or you can use snapshot or serializable transaction isolation level. |
 |
|
|
Kalpa
Starting Member
9 Posts |
Posted - 2008-07-30 : 04:23:37
|
| i am concerned abot deleting only those rows that are inserted not the other ones......can you please suggest me how to wrap it in a transaction and how to proceed than? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-30 : 06:16:45
|
| didnt i give you a solution for this before?http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107588 |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-07-30 : 22:41:01
|
| First way with a single key.begin transactioninsert into processHistory select * from processdaily where processdaily.pk=xxx;delete from processdaily where pk=xxx;commit transaction;it all works or it doesn't.Multiple keys. This will be safe in most transaction modes assuming you have no overlapping groups of records being copied over simultaniously. By default you will see changes in data between statements if rows added or removed by other transactions that meet your criteria):begin transactioninsert into processHistory select * from processdaily where processdaily.something=@someCriteria;delete from processdaily where pk in (select pk from processHistory where processHistory.something=@someCriteria);commit transaction;Otherwise you can set the isolation level and it will all work out so you always get a consistent view for the life of the transaction.I think you need to a) brush up on what transactions and isolation levels are, b) solidify your understanding of how your data is modified (will the data change such that there will be overlapping sets of data between statements).c) put the 2 together to work out which combination will suit your needs. |
|
|
|
|
|
|
deletion of inserted rows