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

SQL Server 觸發器完整指南：從基礎語法到進階實戰

編輯：關於SqlServer

什麼是觸發器？

觸發器是 SQL Server 提供的一種特殊存儲過程，它與資料表事件緊密綁定，不需要程式呼叫或手動啟動，而是在資料表發生 INSERT、DELETE、UPDATE 等操作時自動觸發執行。它的核心價值在於強化資料完整性約束、實現複雜業務規則，以及維護多表之間的參考完整性。

觸發器 vs 存儲過程：核心差異

兩者最大的區別在於執行方式：存儲過程需透過 EXECUTE 調用，而觸發器由事件自動觸發。此外，觸發器可以存取 inserted 和 deleted 兩張邏輯表，這是存儲過程不具備的特性。

查詢資料庫中所有觸發器

SELECT * FROM sysobjects WHERE xtype = 'TR'

觸發器語法結構

CREATE TRIGGER [schema_name.]trigger_name 
ON { table | view } 
[WITH ENCRYPTION] 
{ FOR | AFTER | INSTEAD OF } 
{ INSERT, UPDATE, DELETE } 
AS 
sql_statement

三大類觸發器實戰範例

1. INSERT 觸發器：自動修改關聯資料

CREATE TRIGGER trg_insert_al 
ON al 
FOR INSERT 
AS 
BEGIN
    DECLARE @id INT, @uid INT, @lid INT, @result CHAR(10);
    SELECT @id = id, @uid = uid, @lid = lid, @result = result FROM inserted;
    
    IF (@lid = 4)
    BEGIN
        UPDATE al SET uid = 99 WHERE id = @id;
        PRINT 'lid=4 時自動修改用戶 ID 為 99';
    END
END

2. UPDATE 觸發器：防止邏輯錯誤的資料變更

CREATE TRIGGER trg_update_al 
ON al 
FOR UPDATE 
AS 
BEGIN
    DECLARE @oldlid INT, @newlid INT;
    SELECT @oldlid = lid FROM deleted;
    SELECT @newlid = lid FROM inserted;
    
    IF (@newlid > @oldlid)
    BEGIN
        PRINT '新 lid 不能大於舊 lid';
        ROLLBACK TRAN;
    END
    ELSE
        PRINT '修改成功';
END

3. DELETE 觸發器：保護關聯資料不被誤刪

CREATE TRIGGER trg_delete_al 
ON al 
FOR DELETE 
AS 
BEGIN
    DECLARE @dlid INT;
    SELECT @dlid = lid FROM deleted;
    
    IF EXISTS (SELECT * FROM list WHERE id = @dlid)
    BEGIN
        PRINT '該記錄被引用，無法刪除';
        ROLLBACK TRAN;
    END
    ELSE
        PRINT '刪除成功';
END

觸發器分類詳解

DML 觸發器（資料操縱語言）

針對 INSERT、UPDATE、DELETE 事件啟用。SQL Server 使用 inserted 和 deleted 兩張記憶體中的邏輯表（唯讀）來追蹤資料變更：

inserted 表：儲存新增或修改後的資料
deleted 表：儲存刪除或修改前的資料

DDL 觸發器（資料定義語言）

回應 CREATE、ALTER、DROP 等結構變更事件。

登入觸發器

在使用者建立 SQL Server 會話時觸發，常用於稽核或限制登入行為。

觸發器管理操作圖解

以下操作均在 SQL Server 2005 環境下演示：

建立 UPDATE 觸發器

CREATE TRIGGER mytrigger ON AddTable FOR UPDATE
AS PRINT 'the table was updated! ---By 小豬也無奈'

測試觸發器觸發

修改觸發器

ALTER TRIGGER mytrigger ON AddTable FOR UPDATE
AS PRINT 'updated trigger modified'

查看觸發器定義

EXEC sp_helptext 'mytrigger'

啟用/停用觸發器

-- 停用
DISABLE TRIGGER mytrigger ON DATABASE;
-- 啟用
ENABLE TRIGGER mytrigger ON DATABASE;

進階應用：ROLLBACK 保護資料完整性

當業務邏輯要求特定條件下阻止操作時，可使用 ROLLBACK TRAN 回滾交易：

CREATE TRIGGER trg_protect ON AddTable FOR UPDATE
AS 
BEGIN
    IF EXISTS (SELECT * FROM AddTable WHERE TableName = 'newTable')
    BEGIN
        PRINT '資料保護觸發，操作已回滾';
        ROLLBACK TRAN;
    END
END