Transaction ending in trigger has been canceled

Question

I am using Sql Server 2008 . I have a Trigger that updates my two other tables. I read "Stack" to pass this link, enter a description of the link here , but it does not completely fill my needs. Below is my Trigger

 ALTER TRIGGER [Inventory].[StockUpdationOnIssue] ON [Inventory].[StockIssueDetails] AFTER INSERT AS BEGIN BEGIN TRY BEGIN TRAN INSERT INTO TableA (col1, col2,col3 ) SELECT I.col1,I.col2,si.col3 FROM inserted I INNER JOIN Inventory.StockIssue SI ON SI.StockIssueId = I.StockIssueId INSERT INTO TableB (col1, col2,col3 ) SELECT I.col1,I.col2,si.col3 FROM inserted I INNER JOIN Inventory.StockIssue SI ON SI.StockIssueId = I.StockIssueId COMMIT TRAN END TRY BEGIN CATCH SELECT ERROR_MESSAGE(); RollBack Tran; END CATCH END 

The error is shown to me below ...

Answer 1

You can save the error data in the error log table to come back later to investigate.
Sort of

 ALTER TRIGGER [Inventory].[StockUpdationOnIssue] ON [Inventory].[StockIssueDetails] AFTER INSERT AS BEGIN BEGIN TRY BEGIN TRAN INSERT INTO TableA (col1, col2,col3 ) SELECT I.col1,I.col2,si.col3 FROM inserted I INNER JOIN Inventory.StockIssue SI ON SI.StockIssueId = I.StockIssueId INSERT INTO TableB (col1, col2,col3 ) SELECT I.col1,I.col2,si.col3 FROM inserted I INNER JOIN Inventory.StockIssue SI ON SI.StockIssueId = I.StockIssueId COMMIT TRAN END TRY BEGIN CATCH DECLARE @ErrorMsg VARCHAR(MAX), @ErrorNumber INT, @ErrorProc sysname, @ErrorLine INT SELECT @ErrorMsg = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorProc = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE(); RollBack Tran; INSERT INTO ErrorLog (ErrorMsg, ErrorNumber, ErrorProc, ErrorLine) VALUES (@ErrorMsg, @ErrorNumber, @ErrorProc, @ErrorLine) END CATCH END 

Be a table ErrorLog like:

 CREATE TABLE ErrorLog ( ErrorLogID INT IDENTITY(1,1), ErrorDate DATETIME DEFAULT (GETUTCDATE()), ErrorMsg VARCHAR(MAX), ErrorNumber INT, ErrorProc sysname, ErrorLine INT ) 

Answer 2

I have the same error message. You do not need a transaction inside a trigger because it has a default transaction; those. you don’t need to start a transition or make a transition. But you can use a rollback transition in the catch, and it will be rolled back in case of exceptions.

 ALTER TRIGGER [Inventory].[StockUpdationOnIssue] ON [Inventory].[StockIssueDetails] AFTER INSERT AS BEGIN BEGIN TRY INSERT INTO TableA (col1, col2,col3 ) SELECT I.col1,I.col2,si.col3 FROM inserted I INNER JOIN Inventory.StockIssue SI ON SI.StockIssueId = I.StockIssueId INSERT INTO TableB (col1, col2,col3 ) SELECT I.col1,I.col2,si.col3 FROM inserted I INNER JOIN Inventory.StockIssue SI ON SI.StockIssueId = I.StockIssueId END TRY BEGIN CATCH RollBack Tran; END CATCH END