用SQL Server 2005DDL觸發器對數據庫進行監控
以下的文章主要向大家講述的是SQL Server 2005DDL觸發器監控數據庫的相關變化，添加，刪除或是修改數據庫的相關對象，一旦誤操作，可能會導致大麻煩，需要一個數據庫治理員或開發人員對相關可能受影響的實體進行代碼的重寫。
為了在數據庫結構發生變動而出現問題時，能夠跟蹤問題，定位問題的根源，我們可以利用SQL Server 2005DDL觸發器來記錄類似“用戶建立表”這種變化的操作，這樣可以大大減輕跟蹤和定位數據庫模式的變化的繁瑣程度。
（1）DDL觸發器介紹
DDL 觸發器是一種非凡的觸發器，它在響應數據定義語言 (DDL) 語句時觸發。它們可以用于在數據庫中執行治理任務，例如，審核以及規范數據庫操作。
（2）使用 DDL 觸發器，可以達到以下幾種目的：
A：要防止對數據庫架構進行某些更改。
B：希望數據庫中發生某種情況以響應數據庫架構中的更改。
C：要記錄數據庫架構中的更改或事件。
與標準的DML觸發器一樣，DDL 觸發器在響應事件時執行存儲過程。 但與標準的DML觸發器不同的是，它們并不在響應對表或視圖的 UPDATE、INSERT 或 DELETE 語句時執行存儲過程。 它們主要在響應數據定義語言 (DDL) 語句執行存儲過程。 這些語句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等語句。 執行 DDL 式操作的系統存儲過程也可以激發 DDL 觸發器。
（3）如何使用DDL觸發器
第一步，需要建立一個表，用來記錄數據庫范圍內所有DDL操作。
下面的代碼在PB2K數據庫中創建一個表，用于保存所有DDL操作記錄：
- USE AdventureWork
- GO
- CREATE TABLE AuditLog
- (ID INT PRIMARY KEY IDENTITY(1,1),
- Command NVARCHAR(1000),
- PostTime NVARCHAR(24),
- HostName NVARCHAR(100),
- LoginName NVARCHAR(100)
- )
- GO
第二步，用于保存DDL事件的表在創建好之后，還需要建立一個SQL Server 2005DDL觸發器，監控PB2K數據庫中DDL_DATABASE_LEVEL_EVENTS級別的所有事件：
- CREATE TRIGGER Audit ON DATABASE
- FOR DDL_DATABASE_LEVEL_EVENTS
- AS
- DECLARE @data XML
- DECLARE @cmd NVARCHAR(1000)
- DECLARE @posttime NVARCHAR(24)
- DECLARE @spid NVARCHAR(6)
- DECLARE @loginname NVARCHAR(100)
- DECLARE @hostname NVARCHAR(100)
- SET @data=EVENTDATA()
- SET @cmd=@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(1000)')
- SET@cmd=LTRIM(RTRIM(REPLACE(@cmd,'','')))
- SET@posttime=@data.value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(24)')
- SET@spid=@data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(6)')
- SET@loginname=@data.value('(/EVENT_INSTANCE/LoginName)[1]',
- 'NVARCHAR(100)')
- SET@hostname=HOST_NAME()
- INSERT INTO dbo.AuditLog(Command,PostTime,HostName,LoginName)
- VALUES(@cmd,@posttime,@hostname,@loginname)
- GO
使用 EVENTDATA 函數，可以捕捉有關激發 DDL 觸發器的事件的信息，并將其保存到我們的AuditLog日志表中。EVENTDATA 函數的是返回 xml 值，采用以下的命令進行解析：
- SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))
需要LTRIM和RTRIM是所有的左邊的前導空格和右邊的尾隨空格，而REPLACE函數是用來消除使用SSMS腳本向導時所帶來的回車。
第三步，一旦建立了表和觸發器，我們就可以用以下的操作，來測試看看SQL Server 2005DDL觸發器是否正常運行：
- UPDATE STATISTICS Production.Product
- GO
- CREATE TABLE dbo.Test(col INT)
- GO
- DROP TABLE dbo.Test
- GO
- --View log table
- SELECT * FROM dbo.AuditLog
- GO
執行后，查詢結果如下所示：
圖1: AuditLog日志表查詢結果
（4）小結
通過創建一個日志表來保存所有DDL操作以及創建數據庫級別的SQL Server 2005DDL觸發器，我們能夠成功地捕捉我們的數據庫中所有DDL級的變化，為DBA跟蹤和監視任何改變提供了更強大的功能。
【編輯推薦】
- SQL Server數據庫的臨時表的正確操作步驟
- SQL Server存儲過程的命名標準如何進行？
- 卸載SQL Server 2005組件的正確順序
- 對SQL Server字符串數據類型的具體描述
- SQL Server浮點數據類型的詳細解析
