您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 觸發器全生命週期管理：建立、查看、修改、刪除與實戰範例庫

編輯：關於SqlServer

核心概念速覽

觸發器 = 特殊預存程序，綁定表/檢視表，由 DML 事件（INSERT/UPDATE/DELETE）自動引發，不可显式呼叫。
兩張虛擬表： inserted（新版本資料）、deleted（舊版本資料），結構與觸發表同步，存在 tempdb，觸發結束自動釋放。
AFTER vs INSTEAD OF： AFTER 在約束檢查後執行（僅限表）；INSTEAD OF 取代觸發動作（表/檢視表皆可），可實現不可更新檢視的可更新性。

T-SQL 管理指令完整語法

1. 建立觸發器

CREATE TRIGGER schema.trigger_name
ON schema.table_or_view
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF } [INSERT] [,] [UPDATE] [,] [DELETE]
[NOT FOR REPLICATION]
AS
BEGIN
    SET NOCOUNT ON;
    -- T-SQL 邏輯
END;
GO

2. 修改觸發器

ALTER TRIGGER schema.trigger_name
ON schema.table_or_view
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF } [INSERT] [,] [UPDATE] [,] [DELETE]
[NOT FOR REPLICATION]
AS
BEGIN
    SET NOCOUNT ON;
    -- 修改後邏輯
END;
GO

3. 刪除觸發器

DROP TRIGGER [schema.]trigger_name;
GO

4. 查看觸發器

列出資料庫所有觸發器：

SELECT name, object_id, parent_class_desc, create_date, modify_date, is_disabled
FROM sys.triggers
WHERE parent_class = 1;  -- 1 = 表/檢視表

檢視單一觸發器定義：

EXEC sp_helptext 'trigger_name';
-- 或
SELECT OBJECT_DEFINITION(OBJECT_ID('trigger_name'));

檢視觸發器事件/類型：

SELECT t.name, te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te ON te.object_id = t.object_id
WHERE t.name = 'trigger_name';

5. 啟用/停用

DISABLE TRIGGER trigger_name ON schema.table;  -- 停用
ENABLE TRIGGER trigger_name ON schema.table;   -- 啟用
-- 全資料庫
DISABLE TRIGGER ALL ON DATABASE;
ENABLE TRIGGER ALL ON DATABASE;

實戰範例庫（可直接複製調適）

範例 1：防止貨品狀態為「整理中」時下單

CREATE TRIGGER trg_Order_Insert_CheckStatus
ON Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Goods g ON g.Name = i.GoodsName
        WHERE g.Status = 1  -- 1 = 整理中
    )
    BEGIN
        RAISERROR('該貨品正在整理中，無法下單。', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
GO

範例 2：下單自動扣減庫存

CREATE TRIGGER trg_Order_Insert_DeductStock
ON Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE g
    SET g.Storage = g.Storage - i.Quantity
    FROM Goods g
    JOIN inserted i ON g.Name = i.GoodsName;
END;
GO

範例 3：貨品刪除時級聯清除訂單

CREATE TRIGGER trg_Goods_Delete_Cascade
ON Goods
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE o
    FROM Orders o
    JOIN deleted d ON o.GoodsName = d.Name;
END;
GO

範例 4：保護訂單日期不可人工修改

CREATE TRIGGER trg_Order_Update_ProtectDate
ON Orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(OrderDate)
    BEGIN
        RAISERROR('訂單日期禁止修改。', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
GO

範例 5：強制下單貨品必須存在於主檔

CREATE TRIGGER trg_Order_Insert_ValidateGoods
ON Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1
        FROM inserted i
        LEFT JOIN Goods g ON g.Name = i.GoodsName
        WHERE g.Name IS NULL
    )
    BEGIN
        RAISERROR('下單貨品不存在於主檔。', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
GO

範例 6：同步訂單至外部表（異構同步示意）

ALTER TRIGGER trg_Order_Insert_SyncExternal
ON Orders
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO ExternalOrder (Id, GoodName, Quantity)
    SELECT Id, GoodName, Quantity FROM inserted;
END;
GO

進階主題與避坑指南

主題	關鍵點
遞迴觸發	觸發器修改同表 → 再次觸發。`sp_configure 'nested triggers', 0` 關閉；或用 `TRIGGER_NESTLEVEL()` 判斷層級跳出。
TRUNCATE TABLE	不觸發 DELETE 觸發器，若需攔截請改用權限控制或 CDC。
禁止語句	觸發器內不可執行：`ALTER/CREATE/DROP DATABASE`, `RESTORE`, `RECONFIGURE`, `DISK` 等。
效能	避免游標、發送郵件、呼叫外部 API；大量資料改用 `MERGE`/集合運算；必要時 `SET NOCOUNT ON`。
權限	建立觸發器需 `ALTER` 表權限；執行時以觸發器擁有者權限（非呼叫者）。
加密	`WITH ENCRYPTION` 可隱藏定義，但可被反編譯，非絕對安全。