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