DML Trigger Status Alerts

When databases suddenly stop working, it can be for a number of different reasons. Human error plays a large part, of course, and the DBA needs to know what these various humans are up to. DDL triggers can help alert the DBA to unauthorized tampering with a production system, of course, but DDL triggers can't tell you everything. At some point, you will need to implement your own checks.

A method for tracking DML trigger status on SQL Server 2005

A need for this method – Background

Several weeks ago, during a routine day, our call center started reporting difficulties with sales orders.  Later that same day, our warehouse started reporting problems with our shipping software. They were reporting  missing records when they were scanning packages to ship. Our ERP system has been modified to use DML triggers to push sales orders through the print picking ticket/post sales order/post shipment process as well as interfacing to our shipment software to hand off a shipping record and hand back a tracking number.  The status of several orders indicated that they were getting ‘stuck’ in various stages of this auto-post process, instead of completing as they should. Since we’re still in the process of working out the kinks in this new system and since we’ve had these types of issues before, due to bugs in the modified code, I issued a support call to our vendor. When the vendor answered the call and we started troubleshooting this issue, we found that we were not seeing what we had expected based on past experience.  At one point during the conference call/Webex session, the developer stated, “it is almost as if some triggers are disabled”.  When we began checking the status of triggers, we found that someone had disabled all the triggers on the sales order header table! This precipitated two actions. 

  1. taking appropriate steps to review with senior management the need to put (back) into place a process whereby only internal system administrators will be members of db_owner and external consultants will be removed from this db_owner group and will have to call in to have their logins enabled while they troubleshoot our systems during a monitored Webex session;
  2. a project to ascertain how we might be notified pro-actively in the event a trigger(s) is disabled on our system(s).

A DDL Trigger approach?

At first, it seemed the most obvious technology to use for the project goal was a DDL Trigger.  I had recently re-read Robyn Page’s article on DDL Triggers and their usage, and was keen to try this approach.  However, a problem with the EVENTDATA function surfaced immediately.  Based on examination of BOL it appeared that If the ALTER TABLE command were used to disable / enable a DML trigger the intended information could be captured.  But if the DISABLE TRIGGER, ENABLE TRIGGER, or the GUI of the Management Studio, were used to change a trigger’s status, the information would not be collected.

A quick test using the Studio’s GUI and a prototype DDL trigger proved this assumption correct.  After a few days of prowling Red Gate and Microsoft forums I gathered from various posts that in point of fact, there is no way to capture information about an ENABLE TRIGGER or a DISABLE TRIGGER command in EVENTDATA.  (Since this was the case, Event Notification was also out of the question).

(This assumption about EVENTDATA may be incorrect, but the need was pressing, the facts available supported this conclusion, and so I decided to move on.)

DBMail – the solution (for now)

Since EVENTDATA appears to be useless to the task at hand, the two mechanisms that seemed to meet the pro-active notification requirement – DDL Trigger and Event Notification – were out of the running. We’ve been using the DBMail functionality here for some time.  We’ve found it to be 100% reliable to date.

The concept of a job scheduled to ‘spin’ every minute was settled on.  If the overhead were low, it should not burden the system.  If a trigger were disabled and re-enabled before the job could spin and detect this, then “no harm, no foul”.  Well actually there is a small window of risk, but this was deemed acceptable for our environment.

So, the current (for the present) solution to the problem of getting  g notified about a DML trigger status change is to schedule a TSQL job to run every minute, 24×7, and if the status of a trigger has changed in the last minute, log to a current status table and send an email.

When the job fires and captures a change, this is what the system administrators receive in their inbox:

and when corrected …

Criticisms and Responses

I do not claim that this approach is foolproof, but it is much better than what we had prior to this project. However, I have some criticisms to myself and responses to myself about the project in its current state:

Criticism: Someone with malignant intentions can turn the job off!

Response: Yes, but to do so, they need to examine a long list of jobs and ascertain that a job actually exists to monitor this.  Actions I’m contemplating once I’m satisfied with this approach include placing the TSQL into an encrypted stored procedure (after backing up the code to an offline storage system our sysadmins maintain); this makes the task even more difficult as I will then have a dubiously named job that calls a stored procedure that cannot be listed out.  (Note to self, “is there a way to monitor and detect if a job has been disabled?”)

Criticism: What if Exchange goes down and the alert mail is not delivered?

Response: The table name ‘tbl_triggerAuditRpt’ suggests a report might exist.  It does.  SQL Server Reports is being extensively used and a new “DBA Report” will be added that shows this table (i.e., a report of the current trigger status.  Yes, it is not “Pro-Active”, but it does tend to get watched (as all the DBA Reports do) more closely than most other status reports.

Criticism: You are not capturing an audit trail of trigger status change over time!

Response: There is really no need.  The information of import is that a trigger is disabled.  All that is needed is to: 1) know it is off so it can be turned back on; 2) identify who did this so corrective action can be taken.