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 |
|
sjb500
Starting Member
3 Posts |
 Posted - 2006-11-22 : 12:36:40
|
| I have an AFTER trigger which responds to a table update process. Within this trigger a separate stored procedure is called which does some additional processing. The problem is that if this inner stored procedure fails, for whatever reason, the entire trigger and the initiating update also rollback whereas I want the update to commit.I have tried using various combinations of SAVE TRANSACTION etc but each time I get messages similar to 'The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.'Does anyone have any ideas? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-22 : 12:42:02
|
| What if you start a new transaction before calling the inner SP?Peter LarssonHelsingborg, Sweden |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-11-22 : 15:08:31
|
| a couple thoughts:1) you could issue a "commit tran" in the trigger before you exec you inner sp2) you could have your trigger save a row to a queue table. Then outside the context of your transaction process rows in your queue table. ie: from a job that runs every few seconds or something.3)There are always exceptions but...typically, triggers that launch processes that cause problems indicate that the whole design could be re-vamped.Be One with the OptimizerTG |
|
|
|
sjb500
Starting Member
3 Posts |
Posted - 2006-11-23 : 05:20:19
|
| Thanks for the tips so far - food for thought. |
|
|
|
|
|
|
Rollback Transaction in trigger