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

SQL Server AFTER 觸發器深度解析：從原理到實戰的 8 大關鍵要點

編輯：關於SqlServer

觸發器是什麼？

觸發器是附著在資料表/檢視上的特殊預存程序，當 INSERT、UPDATE、DELETE 發生時**自動執行**。分類：

AFTER (FOR) 觸發器：資料**成功寫入**後執行 (本文重點)。
INSTEAD OF 觸發器：取代原動作執行 (可用於檢視更新、跨表邏輯)。

實戰場景：積分酒店價格自動初始化

部門 A 維護酒店基礎資料 (含價格)。部門 B 需同步建立「積分酒店」，價格 = 基礎價格 × 100。
方案對比：

**結論**：無法修改上游程式時，觸發器是「最小侵入性」補救方案。

AFTER 觸發器 8 大核心知識點

1. 陳述式級觸發，非列級

單一交易中批次異動 100 列，**觸發器只會觸發 1 次**。需在觸發器內處理多列邏輯。

2. 兩張神奇虛擬表：inserted / deleted

方案	優點	缺點
修改部門 A 寫入程式	邏輯集中、即時	跨部門協調難、程式碼耦合、維護風險高
AFTER INSERT 觸發器	零侵入、部署獨立、邏輯封裝在 DB 層	隱性邏輯、除錯較難、效能影響需評估

虛擬表	內容	適用事件
`inserted`	新增的列 / 更新後的新值	INSERT, UPDATE
`deleted`	刪除的列 / 更新前的舊值	DELETE, UPDATE

**無 updated 表**；UPDATE 內部實作為 DELETE + INSERT。

3. AFTER = 動作成功後

若 INSERT 因約束違規失敗，觸發器**完全不會執行**。保證觸發器內邏輯只處理已持久化資料。

4. 零列異動也會觸發！必須早期退出

UPDATE Table SET Col=1 WHERE 1=0 (影響 0 列) 仍會觸發。
**最佳實踐**：觸發器第一行就判斷 @@ROWCOUNT。

CREATE TRIGGER trg_Example ON dbo.TargetTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF @@ROWCOUNT = 0 RETURN; -- 關鍵：零列即刻退出，節省資源、避免鎖定擴大
    
    -- 正常業務邏輯...
END;

5. 禁用 SELECT / PRINT 回傳結果集

觸發器內的 SELECT、PRINT 會向呼叫端 (ADO.NET、ODBC 等) 送出**額外結果集**，導致：

舊版驅動程式拋出「多重結果集」錯誤
ORM (EF/Dapper) 讀取錯誤結果集
效能額外負擔

**除錯時用** RAISERROR('Debug', 0, 1) WITH NOWAIT **寫入錯誤記錄檔**，上線前移除。

6. 觸發器管理指令速查

操作	T-SQL 指令
查看資料表關聯觸發器	`EXEC sp_helptrigger 'TableName';`
查看觸發器定義	`EXEC sp_helptext 'TriggerName';`
刪除觸發器	`DROP TRIGGER TriggerName;`
修改觸發器	`ALTER TRIGGER TriggerName ...`
重新命名	`EXEC sp_rename 'OldName', 'NewName', 'TRIGGER';`

7. 交易管理：觸發器隸屬外部交易

觸發器**不另開交易**，它在觸發它的那個交易內執行。
若觸發器發現錯誤需回滾：

IF @@ERROR <> 0 OR @BusinessRuleViolated = 1
BEGIN
    ROLLBACK TRANSACTION; -- 回滾整個外部交易 (含觸發動作)
    RETURN;
END

**注意**：ROLLBACK 會結束整個批次，後續程式碼不再執行。

8. 多觸發器執行順序控制

同一資料表同一事件可有多個 AFTER 觸發器。用 sp_settriggerorder 定義 First / Last / None。

EXEC sp_settriggerorder 
    @triggername = 'trg_AuditLog',      -- 觸發器名
    @order = 'First',                   -- First / Last / None
    @stmttype = 'INSERT';               -- INSERT / UPDATE / DELETE

First：最先執行 (適合驗證、防禦)
Last：最後執行 (適合審計、通知)
None：順序不保證 (預設)

何時「不該」用觸發器？

檢查約束 (CHECK/FOREIGN KEY/UNIQUE) 能解決 → 用約束 (效能最好、宣告式、優化器可感知)。
預存程序能封裝 → 用預存程序 (可見性高、易除錯、可控交易邊界)。
跨資料庫/跨伺服器操作 → 用 Service Broker / CDC / 應用層事件總線 (觸發器內跨庫易死鎖、分散式交易複雜)。
高頻寫入核心表 → 觸發器增加寫入路徑長度，極端高併發下成瓶頸。

積分酒店價格同步觸發器完整範例

CREATE TRIGGER trg_PointsHotel_PriceInit
ON dbo.PointsHotels
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF @@ROWCOUNT = 0 RETURN;

    UPDATE ph
    SET ph.Price = b.Price * 100,
        ph.LastSync = SYSDATETIME()
    FROM dbo.PointsHotels ph
    INNER JOIN inserted i ON ph.HotelID = i.HotelID
    INNER JOIN dbo.BaseHotels b ON b.HotelCode = ph.HotelCode
    WHERE ph.Price IS NULL OR ph.Price = 0; -- 僅初始化未設價列
END;