When was a table trigger disabled and re-enabled?

Question

I need to know if the enable/disable history of a table trigger is natively tracked by SQL Server.

I've reviewed the system views:
• [sys].[triggers] contains a modify_date field
• [sys].[trigger_events] focuses on the trigger INSERT/UPDATE/DELETE events

Can you recommend any other sources of information about trigger history?

Answer 1

SQL Server doesn't track this information (I half expected to see it in the default trace, but it's not there either). Enable/disable will update modify_date, but you won't be able to distinguish that from a rename or code modification. Also, it will only show you when the last change took place. If you want any other tracking (such as who did it), you'll need to implement auditing or trace. I thought perhaps a DDL trigger could be used as well, but this seems like an intentional functionality gap:

• http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed (abandoned)
• https://connect.microsoft.com/SQLServer/feedback/details/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event (abandoned)
• Fire a DDL TRIGGER when the newer syntax DISABLE TRIGGER is executed

Here is how you can capture this information going forward using an audit:

USE master;
GO

CREATE SERVER AUDIT MyAudit 
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB) 
  WITH (ON_FAILURE = CONTINUE); 
GO

ALTER SERVER AUDIT MyAudit WITH (STATE = ON); 
GO

USE [your_database];
GO

CREATE DATABASE AUDIT SPECIFICATION schema_db_change 
  FOR SERVER AUDIT MyAudit 
  ADD (SCHEMA_OBJECT_CHANGE_GROUP), 
  ADD (DATABASE_OBJECT_CHANGE_GROUP);

ALTER DATABASE AUDIT SPECIFICATION schema_db_change WITH (STATE = ON); 
GO

Now go and disable your trigger, and then run:

SELECT event_time, succeeded, server_principal_name, [object_name], [statement] 
FROM sys.fn_get_audit_file ('C:\Temp\MyAudit*', NULL, NULL);

If you can't use audit due to edition or other issues, you could do this relatively inexpensively using a server-side trace. Just capture SQL:BatchCompleted and optionally filter on TextData LIKE '%disable%trigger%' (You'll have to test if it's better in your scenario to pay the cost for the filter to avoid collecting too much, or just collect more and whittle it down later. Filters can be quite expensive but it depends on the system.)

I'm sure there is also a way to do this with extended events. But XEvents and audit both require 2008+ and you didn't specify version...

Better yet, remove the ability to modify triggers for users who bypass change management. Ideally, you should be able to determine who enabled or disabled a trigger without ever looking at the database, because nobody should be doing that without documenting it.

Answer 2

Currently, only SQL Server Enterprise Edition and SQL Server Developer Edition support the Audit feature in order to track enabling and disabling of triggers To capture these events, you need to create a Server audit specification first - the SQL Server Audit object collects server or database-level actions and groups of actions:

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'c:\audits\', MAXSIZE = 2 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

The next step is to create a Database audit specification at the database level. The audit group we need to capture in our case is SCHEMA_OBJECT_CHANGE_GROUP - there is no audit group that exclusively captures enable/disable trigger events

USE [ACMEDB];
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Query the previously created audit, by using the LIKE operator to narrow down captured entries to the ones related to enable/disable triggers:

SELECT
       event_time AS [Time],
       server_principal_name AS [User],
       object_name AS [Object name],
       Statement
  FROM sys.fn_get_audit_file('c:\audits\ServerAudit*', NULL, NULL)
WHERE
       database_name
       =
       'ACMEDB'
   AND (
       Statement LIKE '%DISABLE%TRIGGER%'
    OR Statement LIKE '%ENABLE%TRIGGER%')ORDER BY
                                          [Time] DESC;

The results will show who disabled/enabled a trigger and when

Although the solution we described is applicable for SQL Server Enterprise Edition and SQL Server Developer Edition users only, a SQL Server Database Audit is fairly simple to be implemented, and can help with tracking when triggers are disabled/enabled

However, the audit information on enabling/disabling triggers can be captured even for the operations executed before "auditing" was installed. You can read more about this in the How to audit your auditing – tracking when triggers are disabled online article

Disclaimer: I work as a Product Support Engineer at ApexSQL

Answer 3

Such operation will likely be recorded in the default administrative trace. The link contains instructions how to view it.

If it's stored in the default location, you can use:

SELECT * 
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\log.trc', default)
GO