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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-23 : 18:31:47
|
Adrian writes "I have a stored procedure which executes multiple updates (updates 2 tables with 2 different statements). Now the second statement updates a table which fires a trigger which further updates 2 other tables (Really just history tables used for reporting purposes).
Here's 2 scenarios which could happen:
1) Now if say, the second update fails (either the update or even the trigger) I need the whole thing to rollback for concurrency (includes the first table update).
2) Same as above, however one of the updates the trigger does (on the history tables) may not be applicable so it will be allowed to fail but everything else must be successful.
Now I'm assuming that I cannot use Autocommit with SQL Server since it doesn't know what I need to do, therefore I probably need to implicitly use transactions to commit/rollback. If this is in fact true, could you please help me with some pseudo code/example since I can't find a good example rolled into one that I can play with. Thanks for your time and any help/tips would be greatly appreciated.
Adrian
Pseudo Code (hopefully this helps):
CREATE PROCEDURE TestTransaction AS
BEGIN TRANSACTION Update_Multitables
UPDATE Table1
UPDATE Table2
/*There is a trigger on Table2 which further updates Table3 & Table4 (scenario 2 would imply that the update on Table4 does not have to succeed for the whole transaction to succeed) */
COMMIT TRANSACTION Update_Multitables
" |
|
|
|
|
|
Transactions