MS SQL Server：DDL 触发器....

MS SQL Server：DDL 触发器

DDL 触发器  (本文转摘于网络）

1. DDL Trigger 触发事件总汇：
在创建用来监视并响应该数据库或服务器实例中的活动的事件通知时，可以指定相应事件类型或事件组。

DDL_DATABASE_LEVEL_EVENTS 包括:
        DDL_TRIGGER_EVENTS,
        DDL_FUNCTION_EVENTS,
        DDL_SYNONYM_EVENTS,
        DDL_SSB_EVENTS,
        DDL_DATABASE_SECURITY_EVENTS,
        DDL_EVENT_NOTIFICATION_EVENTS,
        DDL_PROCEDURE_EVENTS,
        DDL_TABLE_VIEW_EVENTS,
        DDL_TYPE_EVENTS,
        DDL_XML_SCHEMA_COLLECTION_EVENTS,
        DDL_PARTITION_EVENTS,
        DDL_ASSEMBLY_EVENTS

DDL_SERVER_LEVEL_EVENTS 包括：
        DDL_DATABASE_EVENTS,
        DROP_DATABASE,
        DDL_ENDPOINT_EVENTS,
        CREATE_DATABASE,
        DDL_SERVER_SECURITY_EVENTS,
        ALTER_DATABASE


2. DDL Trigger 语法

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE
-- STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL [url=URL]NAME[/url] < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name



-- Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR | AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier>  [ ; ] }
<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name



3. 删除DDL trigger
从系统中删除DDL触发器不能像删除其他对象那样可以简单的通过 DROP object_type object_name 来实现。对于 DDL 触发器，需要在触发器的作用域钱使用一个前缀。

语法：
DROP TRIGGER trigger_name ON { DATABASE | ALL SERVER}


4. EVENTDATA( ) 函数
当事件触发时，尽管这里没有 INSERTED 和 DELETED 表提供检查什么被改变了，但可以使用名为 EVENTDATA( ) 的函数。该函数返回一个 XML 数据类型，其中包含触发器的事件信息。XML数据的基本语法如下，不过，根据被触发的时间不同，函数中的内容也会有所不同。
<SQLInstance>
        <PostTime>date-time</PostTime>
        <SPID>spid</SPID>
        <ComputerName>name</ComputerName>
</SQLInstance>


数据库级别事件具有下面的基本语法，这同以前显示的语法基本语法不同：
<SQLInstance>
        <PostTime>date-time</PostTime>
        <SPID>spid</SPID>
        <ComputerName>name</ComputerName>
        <DatabasesName>name</DatabasesName>
        <UserName>name</UserName>
        <LoginName>name</LoginName>
</SQLInstance>


XML元素 描述如下：
        PostTime：事件触发的日期和时间Ø
    Ø    SPID：指派到导致触发器被触发的代码上的SQLServer进程ID
        ComputerName:导致事件被触发的计算机名称Ø
    Ø    DatabasesName:导致事件被触发的数据库名称
        UserName: 导致事件被触发的用户名称Ø
    Ø    LoginName: 导致事件被触发的用户登录名

实例：如果是工作时间，则不允许修改任何存储过程，否则回滚
DROP TRIGGER TEST_DDL_TRIGGER ON DATABASE
GO
CREATE TRIGGER TEST_DDL_TRIGGER
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
IF DATEPART(hour, GETDATE()) >=9 AND DATEPART(hour, GETDATE()) <= 17
BEGIN
        DECLARE @Message nvarchar(max)
        SELECT @Message =
                'Completing work during core hours. Trying to release -'
                + EVENTDATA().value(
                '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                'nvarchar(max)')
        
        RAISERROR(@Message, 16, 1)
        ROLLBACK
END
GO


例2：创建一个数据库中发生任何行为时都被触发的触发器：
DROP TRIGGER TEST_DDL_TRIGGER_3 ON DATABASE
GO
CREATE TRIGGER TEST_DDL_TRIGGER_3
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
        SELECT EVENTDATA()
GO

接下来创建一个存储过程，来查看返回的XML数据：
CREATE PROCEDURE TEST_TRIGGER_UTIL
AS
        SELECT 'Hello all ~'
GO

返回的 XML 数据如下（经过格式化后）：
<EVENT_INSTANCE>
    <EventType>CREATE_PROCEDURE</EventType>
    <PostTime>2008-03-31T13:53:35.397</PostTime>
    <SPID>56</SPID><ServerName>ZHANGZJSQLEXPRESS</ServerName>
    <LoginName>ZHANGZJDavid_Zhang</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>MySQLServer</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>TEST_TRIGGER_UTIL</ObjectName>
    <ObjectType>PROCEDURE</ObjectType>
    <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
        <CommandText>
            CREATE PROCEDURE TEST_TRIGGER_UTIL
            AS
            SELECT 'Hello all ~'
        </CommandText>
    </TSQLCommand>
</EVENT_INSTANCE>

5. 小结
DML 触发器可以看作是一种特殊的存储过程，可以版主系统保持其完整性，在系统中进行级联更新或强行业务规则。通过 INSERTED 和 DELETED 两个表，我们可以检索哪些列被更新了。DML触发器的本质就是当这两个表中发生数据修改时自动运行的存储过程。

DDL 触发器的构建主要是为了安全，或者根据部门的需求对系统所进行的变更进行通报。通过使用 EVENTDATA( ) 函数，可以在触发器中使用XML信息。