萬盛學電腦網

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

SQL Server DML 觸發器詳解:AFTER 觸發器建立、Deleted/Inserted 魔術表與實戰範例

DML 觸發器依觸發時機分為 AFTER(陳述式完成後)、INSTEAD OF(取代原陳述式)與 CLR 觸發器。本文聚焦 AFTER 觸發器的建立、魔術表運作機制與級聯刪除實戰。

AFTER 觸發器特性

  • 一張表可建立多個同類型 AFTER 觸發器(名稱唯一)。
  • 單一觸發器可涵蓋 INSERT、UPDATE、DELETE 一至三種操作。
  • 觸發器內不可使用:ALTER/CREATE/DROP DATABASE、RESTORE、RECONFIGURE 等系統級指令。

魔術表:Inserted 與 Deleted

兩表結構與基表相同,由 SQL Server 自動建立於 tempdb,僅在觸發器執行期間存在。

實戰:主從表級聯刪除

建立測試資料

USE AdventureWorks;
GO
CREATE TABLE PriTable (OrderId INT IDENTITY(1,1), OrderTotal MONEY);
CREATE TABLE DetailTable (OrderId INT, ProductId INT, ProductCount INT NOT NULL, Price MONEY);

INSERT INTO PriTable VALUES (2100.00), (1000.00);
INSERT INTO DetailTable VALUES (1,1,10,110.00), (1,2,10,100.00), (2,2,10,100.00);

建立 AFTER DELETE 觸發器

CREATE TRIGGER trg_PriTable_CascadeDelete
ON PriTable
AFTER DELETE
AS
BEGIN
    DELETE FROM DetailTable
    WHERE OrderId IN (SELECT OrderId FROM Deleted);

    -- 僅示範用,生產環境避免在觸發器用 PRINT/RAISERROR
    PRINT N'已刪除 DetailTable 相關明細資料';
END;
GO

驗證

DELETE FROM PriTable WHERE OrderId = 1;
SELECT * FROM PriTable;      -- 剩 OrderId=2
SELECT * FROM DetailTable;   -- 僅剩 OrderId=2 的明細

即使 DELETE WHERE OrderId = 3(不存在該筆),觸發器仍會被啟動,此時 Deleted 為空表,NOT IN 子查詢不會誤刪資料。

最佳實務建議

  1. 避免巢狀觸發器過深:預設巢狀層級 32,可用 sp_configure 'nested triggers' 關閉。
  2. 觸發器內勿執行耗時作業(發送郵件、呼叫 Web Service),改用 Service Broker 或非同步佇列。
  3. 使用 SET NOCOUNT ON 減少網路封包。
  4. 錯誤處理:搭配 TRY...CATCH 與 THROW,必要時 ROLLBACK。
  5. 文件化:在觸發器註解說明用途、觸發條件、影響範圍。

AFTER 觸發器是實作資料完整性、稽核追蹤、跨表同步的利器,但需謹慎設計以免造成效能瓶頸或除錯困難。

copyright © 萬盛學電腦網 all rights reserved
操作Inserted 表Deleted 表
INSERT新資料列空
DELETE空被刪除資料列
UPDATE更新後資料列更新前資料列