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中創建觸發器以回應一個或多個事件，例如插入和刪除。