SQL2005 DDL觸發器

在資料庫中有三種資料語言：

DDL（Data Definition Language）:資料定義語言。如Create Drop Alter等

DML（Data Manipulation）:資料操縱語言。如Insert ,Update,Delete

DCL（Data Control Language）:資料控制語言 .如Grant,Revoke等

在2000中隻可以為DML做觸發器，而在2005中，可以為DDL做觸發器，我們叫它DDL觸發器

一：

    Microsoft SQL Server 2005 在資料庫引擎 中引入了多項能提高程式員的開發能力和工作效率的改進和新功能。其中DDL觸發器就是其中之一。

    除了對修改資料的資料操作語言語句（INSERT、UPDATE 和 DELETE）所激發的觸發器提供支援外，資料庫引擎 還包括一類新的觸發器，由修改資料庫對象的 DDL 語句（如以 CREATE、ALTER 或 DROP 開頭的語句）激發。DDL 觸發器可用于稽核或控制對資料庫架構的更改。

    像正常觸發器一樣，DDL 觸發器将激發存儲過程以響應事件，這些語句主要是以 CREATE、ALTER 和 DROP 開頭的語句。DDL 觸發器可用于管理任務，例如稽核和控制資料庫操作。

   僅在運作觸發 DDL 觸發器的 DDL 語句後，DDL 觸發器才會激發。DDL 觸發器無法作為 INSTEAD OF 觸發器使用。

二：

    在響應目前資料庫或伺服器中處理的 Transact-SQL 事件時，可以激發 DDL 觸發器。觸發器的作用域取決于事件。例如，每當資料庫中發生 CREATE TABLE 事件時，都會觸發為響應 CREATE TABLE 事件建立的 DDL 觸發器。每當伺服器中發生 CREATE LOGIN 事件時，都會觸發為響應 CREATE LOGIN 事件建立的 DDL 觸發器。

    在下面的示例中，每當資料庫中發生 DROP TABLE 事件或 ALTER TABLE 事件，都将觸發 DDL 觸發器 safety：

    CREATE TRIGGER safety

    ON DATABASE

    FOR DROP_TABLE, ALTER_TABLE

    AS

       PRINT 'You must disable Trigger "safety" to drop or alter tables!'

       ROLLBACK

    ;

    資料庫範圍内的 DDL 觸發器都作為對象存儲在建立它們的資料庫中。可以在 master 資料庫中建立 DDL 觸發器，這些 DDL 觸發器的行為與在使用者設計的資料庫中建立的 DDL 觸發器一樣。可以從建立 DDL 觸發器的資料庫上下文中的 sys.triggers 目錄視圖中，或通過指定資料庫名稱作為辨別符（例如 master.sys.triggers）來擷取有關這些 DDL 觸發器的資訊。

    伺服器範圍内的 DDL 觸發器作為對象存儲在 master 資料庫中。不同的是，可以從任何資料庫上下文中的 sys.server_triggers 目錄視圖中擷取有關資料庫範圍内的 DDL 觸發器的資訊。

    使用 EVENTDATA 函數，可以捕獲有關激發 DDL 觸發器的事件的資訊。此函數傳回 xml 值。

三：

USE AdventureWorks;
GO
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log 
   (PostTime, DB_User, Event, TSQL) 
   VALUES 
   (GETDATE(), 
   CONVERT(nvarchar(100), CURRENT_USER), 
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
--Test the trigger.
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
GO
SELECT * FROM ddl_log ;
GO
--Drop the trigger.
DROP TRIGGER log
ON DATABASE
GO
--Drop table ddl_log.
DROP TABLE ddl_log
GO      

歡迎溝通及共同進步

越折騰越快樂！