SQL Server创建触发器

在本教程中，将学习如何使用SQL Server CREATE TRIGGER语句来创建新的触发器。

SQL Server CREATE TRIGGER语句简介

CREATE TRIGGER语句用于创建一个新的触发器，只要针对表发生INSERT，DELETE或UPDATE等事件，就会自动触发该触发器。

以下是CREATE TRIGGER语句的语法：

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是一个或多个Transact-SQL，用于在事件发生后执行操作。

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

SQL Server提供了两个专门用于名为INSERTED和DELETED表的触发器的虚拟表。 SQL Server使用这些表来捕获事件发生之前和之后修改行的数据。

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

SQL Server CREATE TRIGGER示例

下面来看一个创建新触发器的示例，将使用示例数据库中的production.products表进行演示。

1. 创建用于记录更改的表

以下语句创建一个名为production.product_audits的表，以便在针对production.products表发生INSERT或DELETE事件时记录信息：

CREATE TABLE production.product_audits(
    change_id INT IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    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. 创建之后DML触发器
首先，要创建新触发器，请在CREATE TRIGGER子句中指定触发器所属的触发器和模式的名称：

CREATE TRIGGER production.trg_product_audit

接下来，在ON子句中指定触发器将在事件发生时触发的表的名称：

ON production.products

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

AFTER INSERT, DELETE

触发器的主体以AS关键字开头：

AS
BEGIN

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

SET NOCOUNT ON;

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

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 AS i
UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        getdate(),
        'DEL'
    FROM
        deleted AS d;

以下将所有部分放在一起，构成一个完整的创建语句：

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 (
    '产品测试(触发器)',
    1,
    1,
    2019,
    999
);

由于INSERT事件，production.products表的production.trg_product_audit触发器被触发。

下面来查看production.product_audits表的内容：

SELECT 
    * 
FROM 
    production.product_audits;

执行上面查询语句，得到以下结果：

注：可以多插入几行，再查询production.product_audits表中的数据。

接下来，执行以下语句从production.products表中删除一行，以测试删除记录时触发器的执行：

DELETE FROM 
    production.products
WHERE 
    product_id = 328;

正如预期的那样，触发器被触发并将已删除的行插入到production.product_audits表中：

SELECT 
    * 
FROM 
    production.product_audits;

执行上面查询语句，得到以下结果：

在本教程中，学习了如何在SQL Server中创建触发器以响应一个或多个事件，例如插入和删除。