SQL Server 2005中使用DDL觸發器監控資料庫變化

iSQlServer發表於2009-04-20
SQLServer觸發器資料庫
新增，刪除或修改資料庫的物件，一旦誤操作，可能會導致大麻煩，需要一個資料庫管理員或開發人員對相關可能受影響的實體進行程式碼的重寫。

　　為了在資料庫結構發生變動而出現問題時，能夠跟蹤問題，定位問題的根源，我們可以利用DDL觸發器來記錄類似“使用者建立表”這種變化的操作，這樣可以大大減輕跟蹤和定位資料庫模式的變化的繁瑣程度。

　　1、DDL觸發器介紹

　　DDL 觸發器是一種特殊的觸發器，它在響應資料定義語言 (DDL) 語句時觸發。它們可以用於在資料庫中執行管理任務，例如，稽核以及規範資料庫操作。

　　使用 DDL 觸發器，可以達到以下幾種目的：

  • 　　 要防止對資料庫架構進行某些更改。
  • 　　 希望資料庫中發生某種情況以響應資料庫架構中的更改。
  • 　　 要記錄資料庫架構中的更改或事件。

　　與標準的DML觸發器一樣，DDL 觸發器在響應事件時執行儲存過程。 但與標準的DML觸發器不同的是，它們並不在響應對錶或檢視的 UPDATE、INSERT 或 DELETE 語句時執行儲存過程。 它們主要在響應資料定義語言 (DDL) 語句執行儲存過程。 這些語句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等語句。 執行 DDL 式操作的系統儲存過程也可以激發 DDL 觸發器。

　　2、如何使用DDL觸發器

　　第一步，需要建立一個表，用來記錄資料庫範圍內所有DDL操作。

　　下面的程式碼在AdventureWorks範例資料庫中建立一個表，用於儲存所有DDL操作記錄：


　　USE AdventureWorks
　　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事件的表在建立好之後，還需要建立一個DDL觸發器，監控AdventureWorks資料庫中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
新增，刪除或修改資料庫的物件，一旦誤操作，可能會導致大麻煩，需要一個資料庫管理員或開發人員對相關可能受影響的實體進行程式碼的重寫。

　　為了在資料庫結構發生變動而出現問題時，能夠跟蹤問題，定位問題的根源，我們可以利用DDL觸發器來記錄類似“使用者建立表”這種變化的操作，這樣可以大大減輕跟蹤和定位資料庫模式的變化的繁瑣程度。

　　1、DDL觸發器介紹

　　DDL 觸發器是一種特殊的觸發器，它在響應資料定義語言 (DDL) 語句時觸發。它們可以用於在資料庫中執行管理任務，例如，稽核以及規範資料庫操作。

　　使用 DDL 觸發器，可以達到以下幾種目的：

  • 　　 要防止對資料庫架構進行某些更改。
  • 　　 希望資料庫中發生某種情況以響應資料庫架構中的更改。
  • 　　 要記錄資料庫架構中的更改或事件。

　　與標準的DML觸發器一樣，DDL 觸發器在響應事件時執行儲存過程。 但與標準的DML觸發器不同的是，它們並不在響應對錶或檢視的 UPDATE、INSERT 或 DELETE 語句時執行儲存過程。 它們主要在響應資料定義語言 (DDL) 語句執行儲存過程。 這些語句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等語句。 執行 DDL 式操作的系統儲存過程也可以激發 DDL 觸發器。

　　2、如何使用DDL觸發器

　　第一步，需要建立一個表，用來記錄資料庫範圍內所有DDL操作。

　　下面的程式碼在AdventureWorks範例資料庫中建立一個表，用於儲存所有DDL操作記錄：


　　USE AdventureWorks
　　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事件的表在建立好之後，還需要建立一個DDL觸發器，監控AdventureWorks資料庫中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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-591035/，如需轉載，請註明出處，否則將追究法律責任。

相關文章