SQL Server Performance Forum – Threads Archive
Triggers transactionHi, 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 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? p.s. do you know if exists a tool that simplify triggers management, for MSQL databases? Thanks,
Try an INSTEAD OF trigger, instead of an FOR | AFTER trigger.
Thanks for your answer. I will… Thanks, Jack Bower
Hi, Do you know if there is another options to do them? I have several triggers I was thinking for something to help me modify that. Thanks, Jack Bower
Is this in SQL Server 2005 version> Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.
This solution is not good…but here it is… create a log table with trigger to execute the procedure…
OR create a job to run every minute to check the log table if the a row exists let the procedure execute… Your first trigger will write a row into the log table which will fire the trigger on the log table…
Hi, what i understand is that u want to have a Asynchronous call to this sp. In that case Notification Service would serve u better. Madhu
ORACLE supports autonomous transactions, I hope a future version of SQL server also. Roji. P. Thomas
Hiand sorry for this delay Yes I am running on server 2005.
Yes the ideea with create a Job sound good, I wil lt ry it and let you know if it’s works as I need to.
I still get this error: ‘The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.’ And the what it pissed me off is the I am not fully understand what that means. Thanks, Jack Bower