萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 觸發器全解析:原理、類型、操作與實戰案例

觸發器本質:事件驅動的特殊存儲過程

觸發器不可顯式調用,僅在 INSERT、UPDATE、DELETE 事件發生時自動激活。核心價值在於實現複雜的業務完整性約束、審計日志、跨表同步 等無法用 DRI(聲明式引用完整性)表達的規則。

兩張魔法表:Inserted 與 Deleted

  • 存在於內存,結構與觸發器作用表完全一致。
  • Inserted:保存 INSERT 新增行、UPDATE 修改後的新值。
  • Deleted:保存 DELETE 移除行、UPDATE 修改前的舊值。
  • 觸發器結束自動銷毀,不可跨批次引用。

兩大類型:AFTER vs INSTEAD OF

特性AFTER 觸發器INSTEAD OF 觸發器
執行時機約束檢查通過後替代觸發動作,約束檢查前
適用對象僅表表、視圖
每表每事件數量可多個僅一個
典型用途審計、同步、事後處理視圖更新、預處理、規避約束

執行流程關鍵差異

  • AFTER:若語句違反約束,觸發器不執行;無法覆蓋約束邏輯。
  • INSTEAD OF:在 Inserted/Deleted 剛建立時執行,可攔截並替換原操作,實現「軟刪除」、「分區路由」等高級模式。

T-SQL 操作速查

-- 創建
CREATE TRIGGER trg_Name
ON {Table|View}
{AFTER | INSTEAD OF} {INSERT, UPDATE, DELETE}
AS BEGIN ... END;

-- 修改
ALTER TRIGGER trg_Name ...  -- 語法同 CREATE

-- 刪除
DROP TRIGGER trg_Name;

-- 查看所有觸發器
SELECT * FROM sysobjects WHERE xtype = 'TR';

-- 查看單個定義
EXEC sp_helptext 'trg_Name';

六大經典實戰場景

  1. 插入訂單前檢查貨品狀態:AFTER INSERT 讀 Inserted 關聯 Goods,狀態=1 則 ROLLBACK。
  2. 下單自動扣減庫存:AFTER INSERT 通過 Inserted 連接 UPDATE Goods SET Storage -= qty。
  3. 貨品刪除級聯訂單:AFTER DELETE 讀 Deleted 刪除關聯訂單。
  4. 禁止手工修改訂單日期:AFTER UPDATE 判斷 UPDATE(OrderDate) 觸發 RAISERROR + ROLLBACK。
  5. 外鍵軟約束:貨品必須存在:AFTER INSERT 檢查 Goods 存在性,否則回滾。
  6. 跨表同步插入:AFTER INSERT 將 Inserted 數據同步寫入另一表。

最佳實踐提醒

  • 觸發器內嚴禁長事務、外部調用(郵件、WebService)、游標循環。
  • 使用 SET NOCOUNT ON 減少網絡往返。
  • 多觸發器同事件時,用 sp_settriggerorder 定義首尾順序。
  • 優先考慮 CHECK、FK、計算列、索引視圖等聲明式方案,觸發器是最後手段。
copyright © 萬盛學電腦網 all rights reserved