SQL Server DDL觸發器

在本教學中，將學習如何使用SQL Server數據定義語言(DDL)觸發器來監視對數據庫對象所做的更改。

SQL Server DDL觸發器簡介

SQL Server DDL觸發器回應伺服器或資料庫事件而不是表數據修改。 這些事件由Transact-SQL語句創建，通常以以下關鍵字之一：CREATE，ALTER，DROP，GRANT，DENY，REVOKE或UPDATE STATISTICS開頭。

例如，只要用戶發出CREATE TABLE或ALTER TABLE語句，就可以編寫DDL觸發器來記錄。

DDL觸發器在以下情況下很有用：

• 記錄資料庫模式中的更改。
• 防止對數據庫模式進行某些特定更改。
• 回應資料庫模式的更改。

以下顯示了創建DDL觸發器的語法：

CREATE TRIGGER trigger_name
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}

在上面語法中，

• trigger_name - 在CREATE TRIGGER關鍵字後指定用戶定義的觸發器名稱。 請注意，不必為DDL觸發器指定模式，因為它與實際的資料庫表或視圖無關。
• DATABASE | ALL SERVER - 如果觸發器回應資料庫範圍的事件，則使用DATABASE;如果觸發器回應伺服器範圍的事件，則使用ALL SERVER。
• ddl_trigger_option - 用於指定ENCRYPTION和/或EXECUTE AS子句。 ENCRYPTION加密觸發器的定義。 EXECUTE AS定義執行觸發器的安全上下文。
• event_type | event_group - 表示導致觸發器觸發的DDL事件，例如，CREATE_TABLE，ALTER_TABLE等。
• event_group是一組event_type事件，例如DDL_TABLE_EVENTS。

觸發器可以訂閱一個或多個事件或事件組。

創建SQL Server DDL觸發器示例

假設要捕獲對數據庫索引所做的所有修改，以便可以更好地監視與這些索引更改相關的資料庫伺服器的性能。

首先，創建一個名為index_logs的新表來記錄索引更改：

CREATE TABLE index_logs (
    log_id INT IDENTITY PRIMARY KEY,
    event_data XML NOT NULL,
    changed_by SYSNAME NOT NULL
);
GO

接下來，創建一個DDL觸發器來跟蹤索引更改並將事件數據插入index_logs表：

CREATE TRIGGER trg_index_changes
ON DATABASE
FOR
    CREATE_INDEX,
    ALTER_INDEX,
    DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO index_logs (
        event_data,
        changed_by
    )
    VALUES (
        EVENTDATA(),
        USER
    );
END;
GO

在觸發器的主體中，使用EVENTDATA()函數返回有關伺服器或資料庫事件的資訊。 該函數僅在DDL或登錄觸發器中可用。

然後，為sales.customers表的first_name和last_name列創建索引：

CREATE NONCLUSTERED INDEX nidx_fname
ON sales.customers(first_name);
GO

CREATE NONCLUSTERED INDEX nidx_lname
ON sales.customers(last_name);
GO

之後，查詢index_changes表中的數據，以檢查觸發器是否正確捕獲了索引創建事件：

SELECT
    *
FROM
    index_logs;

執行上面查詢語句，得到以下結果：

如果單擊event_data列的單元格，則可以按如下方式查看事件的XML數據：

<EVENT_INSTANCE>
  <EventType>CREATE_INDEX</EventType>
  <PostTime>2019-02-27T09:52:06.303</PostTime>
  <SPID>62</SPID>
  <ServerName>DESKTOP-MAXSU</ServerName>
  <LoginName>DESKTOP-MAXSU\hema</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>bk_stores</DatabaseName>
  <SchemaName>sales</SchemaName>
  <ObjectName>nidx_fname</ObjectName>
  <ObjectType>INDEX</ObjectType>
  <TargetObjectName>customers</TargetObjectName>
  <TargetObjectType>TABLE</TargetObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE NONCLUSTERED INDEX nidx_fname
ON sales.customers(first_name)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

在本教學中，學習了如何創建回應一個或多個DDL事件的SQL Server DDL觸發器。