DDL 觸發器是一種特殊的觸發器，它在響應數據定義語言 (DDL) 語句時觸發。它們可以用于在 數據庫 中執 行 管理 任務，例如，審核以 及規范數據庫操作。
DDL 觸發器在 CREATE 、 ALTER 、 DROP 和其他 DDL 語句上操作。它們用于執行管理任務，并強制影響數據庫的業務規則。它們應用于數據庫或 服務器 中某 一類型的所有命令。
數據庫作用域的 DDL 語句——能夠審計的相關類別
服務器作用域的 DDL 語句——能夠審計的相關類別
-- 創建一張審計表，也可以為每類審計定制相關審計表
|
CREATE TABLE dbo.AuditEventsTable ( ID INT NOT NULL IDENTITY, EventType SYSNAME NOT NULL, PostTime DATETIME NOT NULL, SPID SYSNAME NOT NULL, ServerName SYSNAME NOT NULL, LoginName SYSNAME NOT NULL, UserName SYSNAME NOT NULL, DatabaseName SYSNAME NOT NULL, SchemaName SYSNAME NOT NULL, ObjectName SYSNAME NOT NULL, ObjectType SYSNAME NOT NULL, CommandText SYSNAME NOT NULL, EventData XML NOT NULL, Flag INT, MSG VARCHAR(500), CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(ID) ); GO |
-- 所有審計數據均存在在一個 XML 中，相關結構如下
|
EVENTDATA 數據構成 <EVENT_INSTANCE> <EventType>CREATE_TABLE</EventType> <PostTime>2010-03-17T15:59:46.687</PostTime> <SPID>57</SPID> <ServerName>SQLSERVER/TEST2005</ServerName> <LoginName>sa</LoginName> <UserName>dbo</UserName> <DatabaseName>AdventureWorks</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>TETS</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE TABLE TETS(AA VARCHAR(20))</CommandText> </TSQLCommand> </EVENT_INSTANCE> |
-- 根據每種審計，創建相應的審計觸發器，該觸發器可以基于數據庫也可以基于服務器
|
ALTER TRIGGER TRI_AUDIT_CREATE_TABLE ON DATABASE FOR CREATE_TABLE AS DECLARE @EventData XML; DECLARE @ObjectName SYSNAME; DECLARE @MSG VARCHAR(500);
SET @EventData=EVENTDATA(); SET @ObjectName= @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME')+'.'+ @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME');
--此處可以進行相關審核
IF OBJECTPROPERTY(OBJECT_ID(@ObjectName),'TableHasPrimaryKey')=0 BEGIN SET @MSG='Table '+@ObjectName+' does not contain a primary key, You can''t create it'; RAISERROR(@MSG,16,1); ROLLBACK /* INSERT INTO dbo. AuditEventsTable (EventType,PostTime,SPID,ServerName,LoginName,UserName,DatabaseName, SchemaName,ObjectName,ObjectType,CommandText,EventData,Flag,MSG ) VALUES ( @EventData.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(23)'), @EventData.value('(/EVENT_INSTANCE/PostTime)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/SPID)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/ServerName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/UserName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','SYSNAME'), @EventData, 0, @MSG ); */ RETURN; END ELSE -- 記錄成功日志 INSERT INTO dbo. AuditEventsTable (EventType,PostTime,SPID,ServerName,LoginName,UserName,DatabaseName, SchemaName,ObjectName,ObjectType,CommandText,EventData,Flag ) VALUES ( @EventData.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(23)'), @EventData.value('(/EVENT_INSTANCE/PostTime)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/SPID)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/ServerName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/UserName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','SYSNAME'), @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','SYSNAME'), @EventData, 1 ); GO |
-- 進行相關 測試
|
create table test(a varchar(20)) SELECT * FROM dbo. AuditEventsTable |
參考：
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/27569888-f8b5-4cec-a79f-6ea6d692b4ae.htm
http://blog.csdn.net/baoqiangwang/archive/2009/10/19/4700605.aspx
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
