我正在使用Sql Server 2008。我有一个Trigger更新我的其他两个表。我已经阅读了此链接, 在此处输入链接描述,但是并不能完全满足我的需求。下面是我的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…
您可以将错误详细信息保存在“错误日志”表中,以便 稍后再进行调查。就像是
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 )