I am using Sql Server 2008
. I have a Trigger
which updates my two other tables. I have read the Stack over flow this link enter link description here, but it does not full 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
Below error is shown to me...
You can save the error details in a Error Log table, so you can come back later to investigate.
Something like
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
Being table ErrorLog like:
CREATE TABLE ErrorLog
(
ErrorLogID INT IDENTITY(1,1),
ErrorDate DATETIME DEFAULT (GETUTCDATE()),
ErrorMsg VARCHAR(MAX),
ErrorNumber INT,
ErrorProc sysname,
ErrorLine INT
)
I got the same error message. You don't need transaction within the trigger as it has a transaction by default; i.e. you don't need begin tran nor commit tran. But you could use in the catch the rollback tran and it will rollback 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With