Use SQL Server audit to monitor the activation and disabling of triggers

Original: Use SQL Server audit to monitor the activation and disabling of triggers

Usage scenarios:

Sometimes it is found that the business logic in the trigger is not executed, possibly due to a logic error in the trigger. But sometimes it is because some triggers are disabled.

Because SQL Server does not track whether the trigger is enabled or disabled by default. and the command to disable the trigger (Disable Trigger) is not captured in the default trace. However, this information can be retrieved at the server level (not using Profiler), captured sql:stmtcompleted and filtered in the TextData column, but for heavily loaded systems, This will have a relatively large impact.

If you have an Enterprise edition, consider using a new feature:SQL Server Audit. You can use DATABASE AUDIT specification to capture these events.

Steps to use:

First, use the following statement to create server-level monitoring first:

Use master; Gocreate SERVER AUDIT serveraudit  to file (FILEPATH = ' E:\temp\ ', MAXSIZE = 1 GB)--Note change file path with  (on_failure = CON Tinue); Goalter SERVER AUDIT Serveraudit with  (state = ON);

Note that the path needs to be modified and the Temp folder to be opened.

The second step is to create a database-level monitoring:

For this article, we focus on Schema_object_change_group, taking AdventureWorks as an example:

Use AdventureWorks; Gocreate DATABASE AUDIT Specification schema_changefor SERVER AUDIT serverauditadd (Schema_object_change_group) with ( state = ON); GO

The third step is to create a sample table and then create a sample trigger:

CREATE TABLE dbo.splunge (id INT); Gocreate TRIGGER Dbo.splunge_trigger on Dbo.splunge for    insertas    BEGIN        SELECT  1;    Endgo

Fourth step, you can use the following script to check:

SELECT  Event_time,        succeeded,        server_principal_name,        [object_name],        [Statement]from    Sys.fn_get_audit_file (' e:\temp\serveraudit* ', NULL, NULL) WHERE   database_name = ' AdventureWorks ';

As a result, you can see that the records that were created are captured:

Now disable this trigger, and then query again:

DISABLE TRIGGER Dbo.splunge_trigger on Dbo.splunge; Goselect  Event_time,        succeeded,        server_principal_name,        [object_name],        [Statement]from    Sys.fn_get_audit_file (' e:\temp\serveraudit* ', NULL, NULL) WHERE   database_name = ' AdventureWorks ';

The results are as follows:

If you re-enable the trigger, you'll see another line, and if you think you're returning too much data, you can add a filter to the where statement:

And [statement] like '%disable%trigger% '

But sometimes there is a false alarm, for example, there may be the same information in your code. But filtering data is always good for performance and inspection issues.

Summary:

There are many ways to capture events. This feature is only available for Enterprise Edition.

Use SQL Server audit to monitor the activation and disabling of triggers