Triggers transaction | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Triggers transaction

Hi, 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,
Jack Bower
software developer
IATAR Studio
http://iatar.port5.com
Try an INSTEAD OF trigger, instead of an FOR | AFTER trigger.
Hi,
Thanks for your answer. I will… Thanks, Jack Bower
software developer
IATAR Studio
http://iatar.port5.com
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
software developer
IATAR Studio
http://iatar.port5.com
Is this in SQL Server 2005 version> Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
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…
Mohammed U.
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
http://toponewithties.blogspot.com

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
software developer
IATAR Studio
http://iatar.port5.com
]]>