事务在触发器中结束批处理已中止 [英] The Transaction Ended In The Trigger The Batch Has Been Aborted
本文介绍了事务在触发器中结束批处理已中止的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用Sql Server 2008
.我有一个Trigger
,它更新了我的另外两个表.我已经阅读了此链接的堆栈溢出 enter链接说明,但并不能完全满足我的需求.以下是我的Trigger
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
像表ErrorLog一样:
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
)
这篇关于事务在触发器中结束批处理已中止的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文