13.1 SQL Server触发器

SQL Server 触发器(Triggers)

目录

• SQL Server 触发器(Triggers)
  • 简介
  • 创建AFTER触发器（DML）
    • 简介
    • SQL Server CREATE TRIGGER语句
    • 触发器的“虚拟”表：INSERTED和DELETED
    • 创建触发器示例
      • 1）创建用于记录更改的审计表
      • 2）创建一个AFTERDML触发器
      • 3）测试触发器
  • 创建INSTEAD OF触发器（DML）
    • 简介
    • 创建语法
    • 示例
  • SQL Server DDL触发器
    • 简介
    • 示例
  • 禁用触发器
    • 简介
    • 禁用单个触发器
    • 禁用表中的所有触发器
    • 禁用数据库中的所有触发器
  • 启用触发器
    • 简介
    • 示例
      • 启用表中单个触发器
      • 启用表中所有触发器
      • 启用数据库的所有触发器
  • 查看触发器的定义
    • 通过查询系统视图
    • 使用OBJECT_definition函数
    • 使用sp_helptext存储过程
    • 通过SSMS
  • 列出所有触发器
  • 删除触发器
    • 简介
    • 示例
      • A）删除一个DML触发器
      • B）删除一个DDL触发器

简介

SQLServer触发器是响应数据库对象、数据库和服务器事件自动执行的特殊存储过程。SQL Server提供了三种类型的触发器：

• 数据操作语言（DML）触发器，用于对表的INSERT、UPDATE和DELETE事件时而自动调用。
• 数据定义语言（DDL）触发器，用于响应CREATE、ALTER和DROP语句。DDL触发器也会响应某些执行类似DDL操作的系统存储过程而触发。
• 响应Logon事件触发的登录触发器

创建AFTER触发器（DML）

简介

AFTER 触发器（事后触发器）
AFTER 触发器定义了对表执行了 INSERT、UPDATE 或 DELETE 语句操作之后再执行的操作。比如对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用 AFTER 触发器。AFTER 触发器只能在表上指定，且动作晚于约束处理。
每一个表上只能创建一个 INSTEAD OF 触发器，但可以创建多个 AFTER 触发器。

SQL Server CREATE TRIGGER语句

CREATE TRIGGER语句创建一个新触发器，当表发生INSERT、DELETE或UPDATE等事件时，该触发器将自动触发。

语法：

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

其中：

• schema_name是新触发器所属的架构的名称。架构名称是可选的。
• trigger_name是新触发器的名称。
• table_name是应用触发器的表。
• 事件列在AFTER子句中。事件可以是INSERT、UPDATE或DELETE。单个触发器可以触发对表的一个或多个操作。
• NOT FOR REPLICATION选项指示SQL Server在作为复制过程的一部分进行数据修改时不触发触发器。
• sql_statements是一个或多个T-sql，用于在事件发生后执行操作。

触发器的“虚拟”表：INSERTED和DELETED

SQLServer为触发器提供了两个虚拟表，名为INSERTED和DELETED表。SQLServer使用这两个表来捕获事件发生前后修改行的数据。

下表显示了每个事件前后INSERTED和DELETED表的内容：

操作	INSERTED表内容	INSERTED表内容
INSERT	要插入的行	空
UPDATE	更新修改的新行	被更新修改的现有行：
DELETE	空	要删除的行

创建触发器示例

使用如下产品表用于示例：

1）创建用于记录更改的审计表

先创建了一个名为production.product_audits的表。用于在production.products表发生INSERT或DELETE事件时记录信息：

CREATE TABLE production.product_audits(
    change_id INT IDENTITY PRIMARY KEY, --主键
    product_id INT NOT NULL,            --产品Id
    product_name VARCHAR(255) NOT NULL, --产品名
    brand_id INT NOT NULL,              --品牌Id
    category_id INT NOT NULL,           --分类Id
    model_year SMALLINT NOT NULL,       --那一年的产品
    list_price DEC(10,2) NOT NULL,      --标价
    updated_at DATETIME NOT NULL,       --更新时间
    operation CHAR(3) NOT NULL,         --操作方式
    CHECK(operation = 'INS' or operation='DEL')
);

production.product_audits表包含production.products(产品)表的所有列，此外，还有一些列来记录更改，例如updated_at、operation和change_id。

注意，对于重要的数据表，最好是做这样一个审计表来记录对数据的修改，最好再添加上修改人。

2）创建一个AFTERDML触发器

首先，要创建新触发器，请在CREATE TRIGGER子句中指定触发器的名称和触发器所属的模式：

CREATE TRIGGER production.trg_product_audit

然后再指定触发器对应的表：

ON production.products

然后，在AFTER子句中列出将触发触发器的一个或多个事件：

AFTER INSERT, DELETE

触发器的主体逻辑代码以AS关键字开头：

AS
BEGIN

然后，在触发器的主体中，将SET NOCOUNT设置为ON，以禁止在触发触发器时返回受影响的行数。

SET NOCOUNT ON;

每当一行插入production.products表或从production.products表中删除时，触发器将在production.product_audits表中插入一行。插入的数据通过UNION ALL运算符从INSERTED和DELETED表中提供：

完整代码：

CREATE TRIGGER production.trg_product_audit
ON production.products
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO production.product_audits(
        product_id, 
        product_name,
        brand_id,
        category_id,
        model_year,
        list_price, 
        updated_at, 
        operation
    )
    SELECT
        i.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted i
    UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        GETDATE(),
        'DEL'
    FROM
        deleted d;
END

最后，执行整个语句来创建触发器。创建触发器后，您可以在表的触发器文件夹下找到它，如下图所示：

3）测试触发器

咱们对表production.products插入一个新行：

INSERT INTO production.products(
    product_name, 
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES (
    'Test product',
    1,
    1,
    2018,
    599
);

由于是INSERT事件，前面创建的触发器被触发。

我们来检查一下production.product_audits表

SELECT 
    * 
FROM 
    production.product_audits;

没有问题，确实有一条插入记录

然后我们来从production.products表删除一行：

DELETE FROM 
    production.products
WHERE 
    product_id = 322;

触发器应该也被触发

检查一下审计表production.product_audits：

SELECT 
    * 
FROM 
    production.product_audits;

果然有一条删除记录，没有任何问题。

创建INSTEAD OF触发器（DML）

简介

INSTEAD OF 触发器（替代触发器）

INSTEAD OF 触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。

INSTEAD OF 触发器的操作有点类似于完整性约束。在对数据库的操纵时，有些情况下使用约束可以达到更好的效果，而如果采用触发器，则能定义比完整性约束更加复杂的约束。

INSTEAD OF 触发器不仅可在表上定义，还可在带有一个或多个基表的视图上定义，但在作为级联引用完整性约束目标的表上限制应用。

创建语法

CREATE TRIGGER [schema_name.] trigger_name
ON {table_name | view_name }
INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }
AS
{sql_statements}

其中：

• 首先schema_name是新触发器所属的架构的名称。架构名称是可选的。trigger_name是新触发器的名称。
• 其次，指定触发器关联的表(table_name)或视图(view_name)的名称。
• 第三，指定触发器将在INSTEAD OF子句中触发的事件，如INSERT、DELETE或UPDATE。可以响应一个或多个事件。
• 第四，将触发器主体放在AS关键字之后。触发器的主体可以由一个或多个T-SQL语句组成。

示例

使用INSTEAD OF触发器的典型示例是覆盖视图上的插入、更新或删除操作。

假设一个应用程序需要在品牌表（production.brands）中插入一个新品牌。

然而，新品牌应该存储在另一个称为production.brand_approvals（品牌审核）的表中。production.brand_approvals表用于在插入production.brands表之前获得批准审核。
production.brand_approvals表中的品牌审核通过了再插入到production.brands表

要实现这一点，您需要创建一个名为production.vw_brands的视图。production.vw_brands用于应用程序插入新品牌。如果将品牌插入视图，将触发INSTEAD OF触发器来将品牌插入production.brand_approvals审核表。

过程如下：

先创建production.brand_approvals审核表用于存储待审批的品牌:

CREATE TABLE production.brand_approvals(
    brand_id INT IDENTITY PRIMARY KEY,
    brand_name VARCHAR(255) NOT NULL
);

再创建production.vw_brands品牌视图。对应production.brands和production.brand_approvals表：

CREATE VIEW production.vw_brands 
AS
SELECT
    brand_name,
    'Approved' approval_status
FROM
    production.brands
UNION
SELECT
    brand_name,
    'Pending Approval' approval_status
FROM
    production.brand_approvals;

一旦一行插入到production.vw_brands视图，我们通过以下INSTEAD OF触发器将其路由到production.brand_approvals表：

CREATE TRIGGER production.trg_vw_brands 
ON production.vw_brands
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO production.brand_approvals ( 
        brand_name
    )
    SELECT
        i.brand_name
    FROM
        inserted i
    WHERE
        i.brand_name NOT IN (
            SELECT 
                brand_name
            FROM
                production.brands
        );
END

如果production.brands表中不存在新插入的品牌名称，触发器就将新品牌名称插入production.brand_approvals表等待审核。

测试一下，我们向production.vw_brands视图中插入一个新品牌：

INSERT INTO production.vw_brands(brand_name)
VALUES('Eddy Merckx');

此INSERT语句触发了INSTEAD OF触发器以将新行插入到production.brand_approvals表。

此时如果从production.vw_brands视图中查询数据将看到一个新行：

SELECT
	brand_name,
	approval_status
FROM
	production.vw_brands;

再来看看品牌审核表：

SELECT 
	*
FROM 
	production.brand_approvals;

SQL Server DDL触发器

简介

SQLServer DDL触发器响应服务器或数据库事件，而不是表数据修改。这些事件由T-SQL语句创建，通常以以下关键字之一CREATE、ALTER、DROP、GRANT、DENY、REVOKE或UPDATE STATISTICS开头。

比如，只要用户执行CREATE TABLE或ALTER TABLE语句，就可以编写DDL触发器来记录日志。

DDL触发器在以下情况下很有用：

• 记录数据库架构（schema）中的更改。
• 阻止对数据库架构进行某些特定更改。
• 响应数据库架构中的更改。

语法：

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

trigger_name

触发器名字，注意不用指定架构名，因为DDL触发器与实际的表或者视图无关

DATABASE | ALL SERVER

触发器作用范围，如果触发器响应数据库范围的事件，则使用DATABASE；如果触发器响应服务器范围的事件则使用ALL SERVER。

ddl_trigger_option

ddl_trigger_option指定ENCRYPTION和/或EXECUTE AS子句。ENCRYPTION加密触发器的定义。EXECUTE AS定义执行触发器的安全上下文。

event_type | event_group

event_type表示导致触发器触发的DDL事件，例如CREATE_TABLE、ALTER_TABLE等。

event_group是一组event_type事件，如DDL_TABLE_EVENTS。

触发器可以订阅一个或多个事件或事件组。

示例

假设我们想捕获对数据库索引所做的所有修改，方便更好地监视与这些索引更改相关的数据库服务器的性能。

首先，创建一个名为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_logs表中的数据，检查触发器是否正确捕获了索引创建事件:

SELECT 
    *
FROM
    index_logs;

如果单击event_data列的单元格，可以查看事件的详细XML数据：

禁用触发器

简介

有时，出于故障排除或数据恢复的目的，可能需要暂时禁用触发器。为此，可以使用DISABLE TRIGGER语句：

DISABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]    

其中：

• 首先，在DISABLE TRIGGER之后指定触发器所属架构的名称和要禁用的触发器的名称。
• 其次，如果触发器是DML触发器，则指定触发器绑定到的表名或视图。如果触发器是DDL数据库范围的触发器，则使用DATABASE；如果触发器为DDL服务器范围的触发器则使用ALL SERVER。

禁用单个触发器

创建一个会员表用于演示：

CREATE TABLE sales.members (
    member_id INT IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    member_level CHAR(10) NOT NULL
);

下面创建一个触发器，每当新行插入到sales.members中时就会触发该触发器。出于演示目的，触发器只返回一条简单的消息。

CREATE TRIGGER sales.trg_members_insert
ON sales.members
AFTER INSERT
AS
BEGIN
    PRINT 'A new member has been inserted';
END;

现在尝试在表中插入一行：

INSERT INTO sales.members(customer_id, member_level)
VALUES(1,'Silver');

由于INSERT事件，触发器被触发并打印出以下消息：

A new member has been inserted

现在使用如下语句关闭该触发器：

DISABLE TRIGGER sales.trg_members_insert 
ON sales.members;

现在再在会员表插入新行，将不会触发触发器：

INSERT INTO sales.members(customer_id, member_level)
VALUES(2,'Gold');

但是触发器定义仍然在表中。如果在SQL Server Management Studio（SSMS）中查看触发器，将注意到禁用的触发器名称上有一个红色十字图标：

禁用表中的所有触发器

语法：

DISABLE TRIGGER ALL ON table_name;

只需指定表的名称即可禁用属于该表的所有触发器。

禁用数据库中的所有触发器

DISABLE TRIGGER ALL ON DATABASE;

启用触发器

简介

语法：

ENABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]

其中：

• 首先，指定要启用的触发器的名称。也可以指定触发器所属的架构的名称。
• 其次，如果触发器是DML触发器，需指定触发器所属的表。如果触发器是DDL数据库范围的触发器，则使用DATABASE；如果触发器是DDL服务器范围的触发器则使用ALL SERVER。

示例

启用表中单个触发器

比如要启用sales.members表中的触发器sales.trg_members_insert:

ENABLE TRIGGER sales.trg_members_insert
ON sales.members;

启用后，可以通过SQL Server Management Studio查看触发器的状态，如下图所示：

启用表中所有触发器

ENABLE TRIGGER ALL ON table_name;

只需要指定表名就可以了

比如要启用sales.members表所有触发器：

ENABLE TRIGGER ALL ON sales.members;

下图为sales.members的所有触发器的状态：

启用数据库的所有触发器

ENABLE TRIGGER ALL ON DATABASE; 

查看触发器的定义

通过查询系统视图

可以通过sys.sql_modules视图查看触发器定义：

SELECT 
    definition   
FROM 
    sys.sql_modules  
WHERE 
    object_id = OBJECT_ID('sales.trg_members_delete');

在这个查询中，将要获取定义的触发器的名称传递给WHERE子句中的OBJECT_ID()函数。

使用OBJECT_definition函数

SELECT 
    OBJECT_DEFINITION (
        OBJECT_ID(
            'sales.trg_members_delete'
        )
    ) AS trigger_definition;

在这个查询中，将触发器名称传递给OBJECT_ID函数以获取触发器的ID。然后，使用OBJECT_DEFINITION()函数根据触发器的ID获取触发器定义的T-SQL源代码。

使用sp_helptext存储过程

EXEC sp_helptext 'sales.trg_members_delete' ;

sp_helptext存储过程返回已创建对象（在本例中为触发器）的定义。

通过SSMS

要查看DML触发器的定义：

在如下图中表的触发器目录下，右击对应触发器，然后点击Modify（修改），即可查看触发器定义：

列出所有触发器

要列出SQL Server中的所有触发器，可以从sys.triggers视图中查询数据：

SELECT  
    name,
    is_instead_of_trigger
FROM 
    sys.triggers  
WHERE 
    type = 'TR';

输出：

删除触发器

简介

SQL Server DROP TRIGGER语句从数据库中删除一个或多个触发器。以下是删除DML触发器的DROP TRIGGER语句的语法:

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ];

如果要同时删除多个触发器，则需要用逗号分隔。

要删除一个或多个DDL触发器，请使用以下形式的DROP TRIGGER语句：

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON { DATABASE | ALL SERVER };

• DATABASE表示DDL触发器的作用域应用于当前数据库。
• ALL SERVER表示应用于当前服务器的DDL触发器的范围。

要删除LOGON事件触发器，使用以下语法：

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON ALL SERVER;

注意，当删除一个表时，与该表关联的所有触发器也会自动删除。

示例

A）删除一个DML触发器

删除名为sales.trg_members_insert触发器：

DROP TRIGGER IF EXISTS sales.trg_member_insert;

B）删除一个DDL触发器

删除trg_index_changes触发器：

DROP TRIGGER IF EXISTS trg_index_changes;