在SQL Server触发器中登录表 [英] Logging into table in SQL Server trigger
问题描述
我正在编码SQL Server 2005触发器。我想在触发器执行过程中使用INSERT语句到日志表中进行一些记录。当执行过程中发生错误时,我想提出错误并取消导致触发器执行的操作,但不要丢失日志记录。最好的方法是什么?
I am coding SQL Server 2005 trigger. I want to make some logging during trigger execution, using INSERT statement into my log table. When there occurs error during execution, I want to raise error and cancel action that cause trigger execution, but not to lose log records. What is the best way to achieve this?
现在,由于发生ROLLBACK,我的触发器会记录除错误情况以外的所有内容。为了通知调用程序错误,需要使用RAISERROR语句。
Now my trigger logs everything except situation when there is error - because of ROLLBACK. RAISERROR statement is needed in order to inform calling program about error.
现在,我的错误处理代码如下:
Now, my error handling code looks like:
if (@err = 1)
begin
INSERT INTO dbo.log(date, entry) SELECT getdate(), 'ERROR: ' + out from #output
RAISERROR (@msg, 16, 1)
rollback transaction
return
end
推荐答案
另一种可能的选择是使用表变量捕获要存储在永久日志表中的信息。如果给出了ROLLBACK TRANSACTION命令,则不会回滚表变量。示例代码如下...
Another possible option is to use a table variable to capture the info you want to store in your permanent log table. Table variables are not rolled back if a ROLLBACK TRANSACTION command is given. Sample code is below...
--- Declare table variable
DECLARE @ErrorTable TABLE
( [DATE] smalldatetime,
[ENTRY] varchar(64) )
DECLARE @nErrorVar int
--- Open Transaction
BEGIN TRANSACTION
--- Pretend to cause an error and catch the error code
SET @nErrorVar = 1 --- @@ERROR
IF (@nErrorVar = 1)
BEGIN
--- Insert error info table variable
INSERT INTO @ErrorTable
( [Date], [Entry] )
SELECT
getdate(), 'Error Message Goes Here'
RAISERROR('Error Message Goes Here', 16, 1)
ROLLBACK TRANSACTION
--- Change this to actually insert into your permanent log table
SELECT *
FROM @ErrorTable
END
IF @@TRANCOUNT 0
PRINT 'Open Transactions Exist'
ELSE
PRINT 'No Open Transactions'
这篇关于在SQL Server触发器中登录表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!