title: XACT_Abort = ON issue with Try Catch
body: <p>I have this piece of script:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>When I run this code i am getting this error:</p>
  
  <blockquote>
    <p>The current transaction cannot be committed and cannot support
    operations that write to the log file. Roll back the transaction.</p>
  </blockquote>
  
  <p>I already know what causes this issue. </p>
  
  <p>The example is just an example. But I have lots of Business logics inside the trigger that I can't move them out.</p>
  
  <p>So one workaround would be to put <code>Set XACT_Abort off</code> at the beginning of 
  the trigger. However, by doing that we override the default behaviour of SQL dealing with errors in triggers. </p>
  
  <p><strong>My question is if I do that does it expose any issue to the system?</strong></p>
  
  <p>Any other solution except for stripping the trigger off logic would be greatly appreciated.</p>