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資訊。