SQL Server DDL触发器以捕获错误事件 [英] SQL Server DDL Trigger to catch error event

问题描述

大家好



有没有办法在数据库级创建DDL触发器，以便在该数据库中引发错误时运行？



我想要的是创建一个控制日志，记录由于会降低数据库一致性而不会在每个表上创建DML触发器的操作引起的错误。



谷歌先生对我不好，不给我任何答案！



谢谢

Jorge Martins



编辑：

问题在于如何创建一个响应数据库级别的raiseerror事件的触发器，不是在桌面级别。

在表级别我可以创建na INSTEAD OF触发器，在里面我放置一个TRY CATCH块。容易。

我想要的是在数据库级别创建某种错误，而不是为我的数据库中的所有68个表创建INSTEAD OF触发器，或者至少创建其中一些表。

目的是在一方面防止数据库不一致（删除，更新或创建引用/无效值），另一方面用于审计目的，世卫组织，WHEN和WHAT的尝试。

数据库参考规则和完整性已经在大多数情况下阻止了它，但是它遗漏了WHO，WHEN和WHAT。

解决方案

是的，SQL SERVER提供了try catch块捕获错误



你可以在catch块中调用存储过程



你需要创建存储接受消息的程序，并将其添加到日志中。



参考以下链接



http： //technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx

Hi guys

Is there any way to create a DDL trigger, at the Database level, to run when na error is raised in that Database?

What I want is to create a controled log of the errors that arise from actions that would degrade database consistency without creating DML triggers on every table.

Mr. Google hasn't been nice to me and doesn't give me any answer!

Thanks
Jorge Martins

EDITED:
The problema is on how to create a trigger that responds to a raiseerror event at the database level, not at the table level.
At the table level I can create na INSTEAD OF trigger and inside I'd put a TRY CATCH block. Easy.
What I want is to create some sort of error catching at the database level instead of creating INSTEAD OF triggers for all the 68 tables in my DB, or at least, some of them.
The purpose is to, on one side, prevent DB inconsistency (DELETING, UPDATING or CREATING referenced/invalid values), on the other for auditing purposes, WHO, WHEN and WHAT was tried.
Database reference rules and integrity allready prevents that in most cases but it leaves out the WHO, WHEN and WHAT.

解决方案

Yes, SQL SERVER Provides try catch block to catch the errors

you can call a stored procedure in catch block

you need to create stored procedure that will accept message and will add it in log.

refer the below link

http://technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx

这篇关于SQL Server DDL触发器以捕获错误事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！