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.
- 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;
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 |
USE [DBA] -- my database for all audit tables on the system GO /****** Object: Table [dbo].[tbl_triggerAuditRpt] */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_triggerAuditRpt]( [auditDate] [datetime] NOT NULL, [triggerName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [is_disabled] [bit] NOT NULL, [triggerStatus] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [trigger_table] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [who_changed] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [userName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [suserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [originalLogin] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF USE dba -- my database for all audit tables on the system ; IF EXISTS(SELECT * FROM sysobjects WHERE NAME = 'tbl_triggerAuditFires' AND TYPE = 'u') DROP TABLE tbl_triggerAuditFires -- checks for status change in triggers ; USE <snip> -- name of ERP database, put your database to monitor here ; SELECT GETDATE() AS auditDate, sys.triggers.NAME AS triggerName, sys.triggers.is_disabled, CASE WHEN sys.triggers.is_disabled = '1' THEN 'Disabled' ELSE 'Enabled' END AS triggerStatus, sysobjects.NAME AS trigger_table, dt.who_changed, (SELECT (CONVERT([nvarchar](50),user_name(),(0))) ) AS [userName], (SELECT (CONVERT([nvarchar](50),suser_sname(),(0))) ) AS [suserName], (SELECT (CONVERT([nvarchar](50),original_login(),(0)))) AS [originalLogin] INTO dba.dbo.tbl_triggerAuditFires FROM -- if/when user process making change is using ERP login 'psqladm' get the real username (SELECT CASE WHEN SESSION_USER = 'psqladm' THEN (SELECT cast(SUBSTRING( b.HOST_NAME,2,(CHARINDEX('>',b.HOST_NAME)-2) ) AS nvarchar(50)) AS who_changed FROM sys.sql_logins a INNER JOIN sys.dm_exec_sessions b ON a.sid = b.security_id WHERE a.NAME = 'psqladm' AND b.session_id = (select @@SPID)) WHEN SESSION_USER <> 'psqladm' THEN (SELECT CAST(SESSION_USER as NVARCHAR(50))) END as who_changed) as dt, sys.triggers LEFT OUTER JOIN sysobjects ON sys.triggers.parent_id = sysobjects.id ; USE dba ; IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'tbl_triggerChangeDecision' AND TYPE = 'u') DROP TABLE tbl_triggerChangeDecision -- for mailing status changed rows ; SELECT a.* INTO dba.dbo.tbl_triggerChangeDecision FROM dba.dbo.tbl_triggerAuditFires a -- checks for status change INNER JOIN dba.dbo.tbl_triggerAuditRpt b -- holds current status of triggers ON a.triggerName = b.triggerName WHERE a.triggerStatus <> b.triggerStatus ; -- if there has been a status change, then send alert mail to sysadmins IF (SELECT COUNT(*) FROM dba.dbo.tbl_triggerChangeDecision) > 0 BEGIN ; DECLARE @TRGRRCHNG NVARCHAR(MAX) ; SET @TRGRRCHNG = N'<font face="Calibri"><H1>Trigger Change Alert</H1>' + N'<H2>The following trigger(s) had their status changed</H2>' + N'<table border="1">' + N'<tr><th>auditDate</th>' + N'<th>Trigger Name</th>' + N'<th>NEW Trigger Status</th>' + N'<th>Trigger Parent Table</th>' + N'<th>Who Changed</th>' + N'<th>Original Login</th>' + CAST( ( select td = auditDate , '', td = triggerName , '', td = triggerStatus , '', td = trigger_Table , '', td = who_changed , '', td = originalLogin from dba.dbo.tbl_triggerChangeDecision FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</font></table>' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sqlmail', -- your email addresses here @recipients = '<sysadmin@email.com>; <sysadmin2@email.com>', @subject = 'FROM SERVER03 TRIGGER ALERT !!! TRIGGER WAS CHANGED', @body = @TRGRRCHNG , @body_format = 'HTML' ; TRUNCATE TABLE dba.dbo.tbl_triggerAuditRpt -- empty out current status of triggers tbl ; INSERT INTO dba.dbo.tbl_triggerAuditRpt -- add updated status of triggers as new current status SELECT * FROM dba.dbo.tbl_triggerAuditFires ; END ; |
When the job fires and captures a change, this is what the system administrators receive in their inbox:
Trigger Change Alert
The following trigger(s) had their status changed
| auditDate | Trigger Name | NEW Trigger Status | Trigger Parent Table | Who Changed | Original Login |
| 2008-05-27T09:35:36.287 | EAI_orders_insupd | Disabled | orders | dbo | sa |
and when corrected …
| auditDate | Trigger Name | NEW Trigger Status | Trigger Parent Table | Who Changed | Original Login |
| 2008-05-27T09:37:18.177 | EAI_orders_insupd | Enabled | orders | dbo | rvolters |
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.
Load comments