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