Click here to Skip to main content
15,847,427 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Posted
Updated 9-Oct-14 4:43am
v2

1 solution

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
 
Share this answer
 
Comments
Jorge J. Martins 9-Oct-14 9:43am    
Thanks for trying Dipak.
The TRY CATCH part is not the problema.
I've been using that in DML triggers and stored procedures for quite a while.
I've improved my quetion for clarity.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900