以下的文章主要向大家講述的是SQL Server 2005DDL觸發(fā)器監(jiān)控數(shù)據(jù)庫的相關(guān)變化，添加，刪除或是修改數(shù)據(jù)庫的相關(guān)對象，一旦誤操作，可能會導致大麻煩，需要一個數(shù)據(jù)庫治理員或開發(fā)人員對相關(guān)可能受影響的實體進行代碼的重寫。

為了在數(shù)據(jù)庫結(jié)構(gòu)發(fā)生變動而出現(xiàn)問題時，能夠跟蹤問題，定位問題的根源，我們可以利用SQL Server 2005DDL觸發(fā)器來記錄類似“用戶建立表”這種變化的操作，這樣可以大大減輕跟蹤和定位數(shù)據(jù)庫模式的變化的繁瑣程度。

（1）DDL觸發(fā)器介紹

DDL 觸發(fā)器是一種非凡的觸發(fā)器，它在響應數(shù)據(jù)定義語言 (DDL) 語句時觸發(fā)。它們可以用于在數(shù)據(jù)庫中執(zhí)行治理任務，例如，審核以及規(guī)范數(shù)據(jù)庫操作。

（2）使用 DDL 觸發(fā)器，可以達到以下幾種目的：

A：要防止對數(shù)據(jù)庫架構(gòu)進行某些更改。

B：希望數(shù)據(jù)庫中發(fā)生某種情況以響應數(shù)據(jù)庫架構(gòu)中的更改。

C：要記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。

與標準的DML觸發(fā)器一樣，DDL 觸發(fā)器在響應事件時執(zhí)行存儲過程。 但與標準的DML觸發(fā)器不同的是，它們并不在響應對表或視圖的 UPDATE、INSERT 或 DELETE 語句時執(zhí)行存儲過程。 它們主要在響應數(shù)據(jù)定義語言 (DDL) 語句執(zhí)行存儲過程。 這些語句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等語句。 執(zhí)行 DDL 式操作的系統(tǒng)存儲過程也可以激發(fā) DDL 觸發(fā)器。

（3）如何使用DDL觸發(fā)器

第一步，需要建立一個表，用來記錄數(shù)據(jù)庫范圍內(nèi)所有DDL操作。

下面的代碼在PB2K數(shù)據(jù)庫中創(chuàng)建一個表，用于保存所有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事件的表在創(chuàng)建好之后，還需要建立一個SQL Server 2005DDL觸發(fā)器，監(jiān)控PB2K數(shù)據(jù)庫中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 函數(shù)，可以捕捉有關(guān)激發(fā) DDL 觸發(fā)器的事件的信息，并將其保存到我們的AuditLog日志表中。EVENTDATA 函數(shù)的是返回 xml 值，采用以下的命令進行解析：

SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'',''))) 

需要LTRIM和RTRIM是所有的左邊的前導空格和右邊的尾隨空格，而REPLACE函數(shù)是用來消除使用SSMS腳本向?qū)r所帶來的回車。

第三步，一旦建立了表和觸發(fā)器，我們就可以用以下的操作，來測試看看SQL Server 2005DDL觸發(fā)器是否正常運行：

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 

執(zhí)行后，查詢結(jié)果如下所示：

圖1: AuditLog日志表查詢結(jié)果

（4）小結(jié)

通過創(chuàng)建一個日志表來保存所有DDL操作以及創(chuàng)建數(shù)據(jù)庫級別的SQL Server 2005DDL觸發(fā)器，我們能夠成功地捕捉我們的數(shù)據(jù)庫中所有DDL級的變化，為DBA跟蹤和監(jiān)視任何改變提供了更強大的功能。

【編輯推薦】

1. SQL Server數(shù)據(jù)庫的臨時表的正確操作步驟
2. SQL Server存儲過程的命名標準如何進行？
3. 卸載SQL Server 2005組件的正確順序
4. 對SQL Server字符串數(shù)據(jù)類型的具體描述
5. SQL Server浮點數(shù)據(jù)類型的詳細解析