SQL Server Instead Of触发器

在本教程中，将学习如何使用SQL Server INSTEAD OF触发器通过视图将数据插入基础表。

INSTEAD OF触发器简介

INSTEAD OF触发器是一种触发器，用于跳过对表或视图的INSERT，DELETE或UPDATE语句，并执行触发器中定义的其他语句。根本不会发生实际的插入，删除或更新操作。

换句话说，INSTEAD OF触发器会跳过DML语句并执行其他语句。

SQL Server INSTEAD OF触发器语法

以下说明了如何创建INSTEAD OF触发器的语法：

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

在这个语法中：

• 首先，在CREATE TRIGGER子句中指定触发器的名称以及触发器所属的模式的名称。
• 其次，指定触发器关联的表或视图的名称。
• 第三，指定触发器将在INSTEAD OF子句中触发的事件，如INSERT，DELETE或UPDATE。 可以调用触发器以响应一个或多个事件。
• 第四，将触发器主体放在AS关键字之后。 触发器的主体可能包含一个或多个Transact-SQL语句。

SQL Server INSTEAD OF触发器示例

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

假设，应用程序需要将新品牌插入到production.brands表中。 但是，在插入到production.brands表之前，新品牌应存储在另一个名为production.brand_approvals的表中以供批准。

为此，需要为应用程序创建名为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.brands和production.brand_approvals表创建名为production.vw_brands的新视图：

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('捷马');

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

如果查询production.vw_brands表中数据，将看到一条新行记录信息：

SELECT
 brand_name,
 approval_status
FROM
 production.vw_brands;

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

在本教程中，学习了SQL Server INSTEAD OF触发器以及如何创建INSTEAD OF触发器以通过视图将数据插入基础表。