登录到 SQL Server 触发器中的表
我正在编写 SQL Server 2005 触发器。我想在触发器执行期间使用 INSERT 语句到我的日志表中进行一些日志记录。当执行过程中发生错误时,我想引发错误并取消导致触发器执行的操作,但不丢失日志记录。实现这一目标的最佳方法是什么?
现在,我的触发器会记录除出现错误时的情况之外的所有内容 - 因为回滚。需要 RAISERROR 语句来通知调用程序有关错误的信息。
现在,我的错误处理代码如下所示:
if (@err = 1)
begin
INSERT INTO dbo.log(date, entry) SELECT getdate(), 'ERROR: ' + out from #output
RAISERROR (@msg, 16, 1)
rollback transaction
return
end
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?
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
另一种可能的选择是使用表变量来捕获要存储在永久日志表中的信息。如果给出 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...
这里的问题是日志记录是修改数据的事务的一部分。嵌套事务在这里没有帮助。您需要的是将记录操作放入单独的上下文(连接)中,即使其独立于当前事务。
我想到了两个选择:
HTH
The problem here is that logging is part of transaction that modifies your data. Nested transactions will not help here. What you need is to put you logging actions into a separate context (connection), i.e. make it independent from you current transaction.
Two options come to my mind:
HTH
不知道我是否想得太简单,但为什么不只是更改错误处理程序的顺序以在回滚之后插入?
Don't know if I'm thinking too simple, but why not just change the order of the error handler to insert AFTER the rollback??
查看触发器中的错误处理。
Checkout error handling in triggers.