使用SQLServer Audit來監控觸發器的啟用、禁用情況

標籤：

原文: 使用SQLServer Audit來監控觸發器的啟用、禁用情況

 使用情景：      

       有時候會發現在觸發器中的商務邏輯沒有執行，可能是因為觸發器的邏輯錯誤所引起的。但是有時候卻是因為一些觸發器被禁用了。

 

       由於SQLServer預設不跟蹤觸發器的啟用還是禁用。且禁用觸發器的命令（Disable Trigger）不在預設跟蹤裡面捕獲。但是可以在伺服器層級的跟蹤（不是使用Profiler）擷取這些資訊，捕獲SQL:StmtCompleted並在TextData列篩選，但是對於負載較重的系統，這樣會有比較大的影響。

 

       如果你的是企業版，可以考慮使用一個新特性：SQL Server Audit。可以使用DATABASE AUDIT SPECIFICATION來捕獲這些事件。

 

使用步驟：

 

第一步，使用以下語句先建立伺服器層級監控：

 

USE master;GOCREATE SERVER AUDIT ServerAudit  TO FILE (FILEPATH = ‘E:\temp\‘, MAXSIZE = 1 GB)--注意變更檔路徑  WITH (ON_FAILURE = CONTINUE);GOALTER SERVER AUDIT ServerAudit  WITH (STATE = ON);

注意，路徑需要修改，temp檔案夾也要實現開啟。

第二步，建立資料庫層級監控:

 

 對於本文，我們關注SCHEMA_OBJECT_CHANGE_GROUP，以AdventureWorks為例子：

USE AdventureWorks;GOCREATE DATABASE AUDIT SPECIFICATION schema_changeFOR SERVER AUDIT ServerAuditADD (SCHEMA_OBJECT_CHANGE_GROUP)WITH (STATE = ON);GO

 

 

第三步，建立一個樣本表，然後建立一個樣本觸發器：

 

CREATE TABLE dbo.splunge ( id INT ) ;GOCREATE TRIGGER dbo.splunge_trigger ON dbo.splunge    FOR INSERTAS    BEGIN        SELECT  1 ;    ENDGO

 

 

第四步，可以使用以下指令碼來檢查：

 

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‘ ;

 

結果如下，可以看到捕獲了建立的記錄：

 

 

現在來禁用這個觸發器，然後再查詢一下：

 

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‘ ;

結果如下：

 

如果你重新啟用這個觸發器，你會再看到另外一行，如果覺得返回資料太多，可以在where語句中添加篩選：

AND [statement] LIKE ‘%disable%trigger%‘

 

     但是有時候會存在誤判，比如，在你的代碼裡面可能存在同樣的資訊。但是篩選資料對效能和檢查問題總是有好處的。

 

小結：

        捕獲事件有很多種方式。此功能僅限2008企業版使用。

使用SQLServer Audit來監控觸發器的啟用、禁用情況