Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thank you.

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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

https://connect.microsoft.com/SQLServer/feedback/details/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event

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.

share|improve this answer
add comment

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

enter image description here 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

share|improve this answer
add comment

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
share|improve this answer
    
It is not in the default trace, no. –  Aaron Bertrand Oct 19 '12 at 16:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.