dimanche 28 juin 2015

XACT_Abort = ON issue 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 am getting this error: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

I already know what causes this issue. The example is just an example. But I have lots of Business logics inside the trigger that I can't move them out. So one workaround would be to put Set XACT_Abort off at the beginning of trigger. However, by doing that we override the default behaviour of SQL dealing with triggers. My question is if I do that does it expose any issue to the system? Any other solution except for stripping the trigger off logic would be greatly appreciated.

Ta.

Aucun commentaire:

Enregistrer un commentaire