XACT_Abort = ON problem with Try Catch
I have this piece of script:
Create Table AA (ID int identity(1,1), Col1 varchar(10))
Create Table BB (ID int identity(1,1), Col1 varchar(10))
GO
Create proc p6
as
insert into AA
(Col1)
Values('')
GO
Create Trigger [dbo].[TR_AA] on [dbo].[AA]
After insert
As
--Set XACT_Abort off
Select 1/0
GO
Begin Try
Begin Tran
Select @@TRANCOUNT
exec p6
Commit Tran
End Try
Begin Catch
insert into BB(Col1)Values('')
Select * from AA
--Select XACT_STATE()
Rollback Tran
End Catch
Select Count(*) from AA
GO
When I run this code I get this error:
The current transaction cannot be completed and cannot support the operations that are written to the log file. Cancel the transaction.
I already know what is causing this problem.
Example - Example. But I have a lot of business logic inside the trigger that I can't pull them out.
Thus, one way would be to put the Set XACT_Abort off
trigger at the beginning. However, by doing this, we are overriding the default SQL trigger error behavior.
My question is, am I doing this to identify any problems on the system?
Any other solution would be helpful except to exclude the flip-flop circuitry.
source to share
If you want to save your transaction XACT_ABORT = OFF
should help.
However, the installation XACT_ABORT = OFF
does not guarantee that the transaction will continue in all cases. It depends on the severity of the error.
When SET XACT_ABORT is off, in some cases only the Transact-SQL statement that raised this error is rolled back and the transaction continues processing. Depending on the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is off.
Another problem with XACT_ABORT = OFF
is that now your error handling and data saving process are different in code with different settings XACT_ABORT
.
EDIT
These links can help.
source to share