title: SQL Trigger - Commit Update and CATCH Block of Procedure
body: <p>I have a trigger on a table that executes a stored procedure. The executed stored procedure has a TRY/CATCH, so that if there is an error, a row is inserted into a log table.</p>
  
  <p>When the stored procedure fails, I'm getting the following error:</p>
  
  <pre><code>The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
  </code></pre>
  
  <p>How do I make it so the update get committed and the CATCH in the stored procedure is also executed? If I add: </p>
  
  <pre><code>IF @@TRANCOUNT &gt; 0 
  ROLLBACK TRAN
  </code></pre>
  
  <p>to the stored procedure, then I get the following error:</p>
  
  <pre><code>The transaction ended in the trigger. The batch has been aborted.
  </code></pre>
  
  <p>Trigger:</p>
  
  <pre><code>ALTER TRIGGER [dbo].[trigger123] ON [dbo].[tbl321]
  AFTER UPDATE
  AS
  BEGIN
  SET NOCOUNT ON;
  IF UPDATE (status) 
  BEGIN
  
   IF EXISTS (--some condition)
  BEGIN
  EXEC SProc 
  END
  
  END
  END
  </code></pre>
  
  <p>SProc:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SProc ]
  AS  
  BEGIN  
  
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  
  BEGIN TRY
  
     select @sql = '
     declare @error1 varchar(255),
     @error2 varchar(255),
     @error3 varchar(255)
  
     Exec SomeDB.DBO.ConfirmStatus  ''A10594'',@error1 output,@error2 output,@error3 output
  
     if ISNULL(@error1,0) &lt;&gt; 0
     begin
          set @error1 = ISNULL(@error2,'''') + '' '' + ISNULL(@error3,'''') + '' '' + ISNULL(@error1,0)
          RAISERROR (@error1, 16, 1)
     end'
     from jobs j
  
     exec(@sql) at [linked_server]
  
     update status
      set status_prev = 1
  
  END TRY
  
  BEGIN CATCH
  
  SELECT
  @ErrorMessage = ERROR_MESSAGE(),
  @ErrorSeverity = ERROR_SEVERITY(),
  @ErrorState = ERROR_STATE()
  
  INSERT INTO error_log (error_datetime, [error_message])
  SELECT
      GETDATE(),
      'Msg: ' + ISNULL(CONVERT(VARCHAR, ERROR_NUMBER()), 'N/A') + ', Level: ' + ISNULL(CONVERT(VARCHAR, @ErrorSeverity), 'N/A') + ', Line: ' + ISNULL(CONVERT(VARCHAR, ERROR_LINE()), 'N/A') + ', Error: ' + ISNULL(@ErrorMessage, 'N/A')
  
  END CATCH
  END
  </code></pre>