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觸發器以通過視圖將數據插入基礎表。