November 7, 2016 at 1:29 am
I have a problem with calling "EXEC sp_settriggerorder" when @TriggerName is not exists.
After calling in TRY / CATCH section remains open transaction.
I do not know why this occurs.
example 1/
SELECT 1, @@trancount
EXEC sp_settriggerorder @TriggerName = 'trigger', @Order = 'FIRST', @StmtType = 'INSERT'
SELECT 2, @@trancount
SELECT 4, @@trancount
----------- -----------
1 0
Msg 15165, Level 16, State 1, Procedure sp_settriggerorder, Line 142
Could not find object 'trigger' or you do not have permission.
----------- -----------
2 0
----------- -----------
4 0
example 2/
BEGIN TRY
SELECT 1, @@trancount
EXEC sp_settriggerorder @TriggerName = 'trigger', @Order = 'FIRST', @StmtType = 'INSERT'
SELECT 2, @@trancount
END TRY
BEGIN CATCH
SELECT 3, @@TRANCOUNT
END CATCH
SELECT 4, @@trancount
ROLLBACK
----------- -----------
1 0
----------- -----------
3 1
------------------------------
Could not find object 'trigger' or you do not have permission.
----------- -----------
4 1
Thanks
November 7, 2016 at 1:42 am
Question, do you see the trigger in sys.triggers?
😎
SELECT
OBJECT_NAME(STRG.parent_id) AS TABLE_NAME
,STRG.name AS TRIGGER_NAME
FROM sys.triggers STRG;
November 7, 2016 at 2:12 am
It's just a question of principle. Why when using the section try / catch remains open transactions?
November 7, 2016 at 2:22 am
rajnoha (11/7/2016)
It's just a question of principle. Why when using the section try / catch remains open transactions?
In this case it is the poor coding of sys.sp_settriggerorder which is throwing its toys out of the pram after a begin transaction statement which doesn't have adequate error handling.
😎
November 7, 2016 at 2:31 am
Yes, it's an explanation. But if the procedure call without section TRY / CATCH then no transaction is open - Example 1.
November 7, 2016 at 3:59 am
rajnoha (11/7/2016)
Yes, it's an explanation. But if the procedure call without section TRY / CATCH then no transaction is open - Example 1.
When wrapped in a try/catch, the exit clause (via goto) in the procedure isn't executed but that clause contains commit transaction statement which closes the open transaction. As there is no rollback or commit in the top level catch clause, then the transaction is left open.
😎
November 7, 2016 at 5:28 am
Eirikur Eiriksson (11/7/2016)
rajnoha (11/7/2016)
Yes, it's an explanation. But if the procedure call without section TRY / CATCH then no transaction is open - Example 1.When wrapped in a try/catch, the exit clause (via goto) in the procedure isn't executed but that clause contains commit transaction statement which closes the open transaction. As there is no rollback or commit in the top level catch clause, then the transaction is left open.
😎
You could try adding
SET XACT_ABORT ON;
to your proc to handle this without the need for any other additional code.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply