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触发器。