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