关于SQLServer2005的学习笔记——系统触发器

DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库 中执行管理 任务，例如，审核以及规范数据库操作。

DDL 触发器在 CREATE 、 ALTER 、 DROP 和其他 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