When was a table trigger disabled and re-enabled?

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?

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.

StackExchange Database Administrators Q#27255, answer score: 11