December 3, 2004 at 9:28 am
I've recently come across an issue where a trigger used for auditing purposes has not been firing for several months. The user did not notice any error until after a period end by which time the data that was being audited had been cleared down. Bit of a problem.
What is the easiest way to monitor the successful/unsuccessful completion of triggers? Is there an easy way to fire off alerts when a trigger fails?
Many thanks.
December 4, 2004 at 1:22 pm
What is the easiest way to monitor the successful/unsuccessful completion of triggers? Is there an easy way to fire off alerts when a trigger fails?
If you are sure that the trigger is enabled at all, the easiest way I could think of is to write a value into a control table which you will check then.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 6, 2004 at 10:05 am
Make sure what "where" clauses you have in your trigger and do they meet the criteria.
As Frank said, you can create a temp table and just add some test insert sql statements in there.
You can also turn on Profiler and see if the trigger is there.
December 7, 2004 at 2:27 am
Thanks, but its not quite what I was looking for.
I was hoping something might be possible along the lines of :
IF FAILED
{ RAISERROR(......)}
That would be the ideal solution, so alerts can be triggered. Triggering alerts on successful execution of the trigger isn't really practical as it fires many times an hour.
Reports aren't run on the audit data generated by the trigger until period ends. There must be some way of firing an alert when a trigger fails? I'll have another look at it myself later.
December 8, 2004 at 6:45 pm
This is not a perfect solution, but allows you to test completed stages of the tirgger logic. It is also more complicated to implement. The trigger must fire or it will not work.
1) If required make deleted and inserted available to the new procedure.
2) encapsulate the trigger logic inside a stored procedure. Set return status at critical stages of the procdure logic.
3) execute procedure from inside trigger, see BOL
EXECUTE @return_status = procedure_name
4) At end of trigger test return status and raise an error if required
Cheers,
Win
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply