DDL 觸發(fā)器是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語言 (DDL) 語句時(shí)觸發(fā)。它們可以用于在 數(shù)據(jù)庫 中執(zhí) 行 管理 任務(wù)，例如，審核以 及規(guī)范數(shù)據(jù)庫操作。
DDL 觸發(fā)器在 CREATE 、 ALTER 、 DROP 和其他 DDL 語句上操作。它們用于執(zhí)行管理任務(wù)，并強(qiáng)制影響數(shù)據(jù)庫的業(yè)務(wù)規(guī)則。它們應(yīng)用于數(shù)據(jù)庫或 服務(wù)器 中某 一類型的所有命令。
數(shù)據(jù)庫作用域的 DDL 語句——能夠?qū)徲?jì)的相關(guān)類別
服務(wù)器作用域的 DDL 語句——能夠?qū)徲?jì)的相關(guān)類別
-- 創(chuàng)建一張審計(jì)表，也可以為每類審計(jì)定制相關(guān)審計(jì)表
|
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 |
-- 所有審計(jì)數(shù)據(jù)均存在在一個(gè) XML 中，相關(guān)結(jié)構(gòu)如下
|
EVENTDATA 數(shù)據(jù)構(gòu)成 <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> |
-- 根據(jù)每種審計(jì)，創(chuàng)建相應(yīng)的審計(jì)觸發(fā)器，該觸發(fā)器可以基于數(shù)據(jù)庫也可以基于服務(wù)器
|
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');
--此處可以進(jìn)行相關(guān)審核
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 |
-- 進(jìn)行相關(guān) 測試
|
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
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
