觸發器分類
| 類型 | 觸發時機 | 適用對象 | 說明 |
|---|
| AFTER (FOR) | INSERT/UPDATE/DELETE 完成後 | 資料表 | 可多個，順序可用 sp_settriggerorder 設定 |
| INSTEAD OF | 取代原動作執行 | 資料表、檢視表 | 每表每動作僅一個，常用於檢視表可更新化 |
兩張魔術表
- inserted：新增/修改後的資料映像（UPDATE 視為刪舊增新，兩表皆有資料）。
- deleted：刪除/修改前的資料映像。
- 皆為記憶體常駐、唯讀、結構與基表一致，觸發器結束自動銷毀。
實戰範例庫
1. AFTER INSERT：同步新增關聯資料
CREATE TRIGGER tgr_classes_insert ON classes FOR INSERT
AS
DECLARE @id INT, @name VARCHAR(20);
SELECT @id = id, @name = name FROM inserted;
INSERT INTO student (name, age, class_id) VALUES (@name + CAST(@id AS VARCHAR), 18+@id, @id);
PRINT '學生同步新增成功';
2. AFTER DELETE：刪除前備份
CREATE TRIGGER tgr_classes_delete ON classes FOR DELETE
AS
IF OBJECT_ID('classesBackup','U') IS NOT NULL
INSERT INTO classesBackup SELECT name, createDate FROM deleted;
ELSE
SELECT * INTO classesBackup FROM deleted;
PRINT '備份完成';
3. AFTER UPDATE：級聯修改關聯表
CREATE TRIGGER tgr_classes_update ON classes FOR UPDATE
AS
DECLARE @old VARCHAR(20), @new VARCHAR(20);
SELECT @old = name FROM deleted;
IF EXISTS (SELECT * FROM student WHERE name LIKE '%'+@old+'%')
BEGIN
SELECT @new = name FROM inserted;
UPDATE student SET name = REPLACE(name, @old, @new) WHERE name LIKE '%'+@old+'%';
PRINT '級聯更新完成';
END
4. 欄位級 UPDATE 觸發：防改建立日期
CREATE TRIGGER tgr_classes_update_col ON classes FOR UPDATE
AS
IF UPDATE(createDate)
BEGIN
RAISERROR('建立日期不可修改', 16, 1);
ROLLBACK TRAN;
END
5. INSTEAD OF DELETE：自訂刪除邏輯（先刪子表再刪主表）
CREATE TRIGGER tgr_classes_insteadOf ON classes INSTEAD OF DELETE
AS
DECLARE @id INT, @name VARCHAR(20);
SELECT @id = id, @name = name FROM deleted;
DELETE student WHERE cid = @id;
DELETE classes WHERE id = @id;
PRINT '級聯刪除完成';
6. 通用稽核觸發器
CREATE TRIGGER tgr_student_log ON student AFTER INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
INSERT INTO log(action) VALUES('updated');
ELSE IF EXISTS(SELECT 1 FROM inserted)
INSERT INTO log(action) VALUES('inserted');
ELSE
INSERT INTO log(action) VALUES('deleted');
7. 資料驗證觸發器（年齡必須 ≥ 18）
CREATE TRIGGER tgr_valid_data ON student AFTER INSERT
AS
DECLARE @age INT; SELECT @age = age FROM inserted;
IF @age < 18 BEGIN RAISERROR('年齡不足',16,1); ROLLBACK TRAN; END
管理指令速查
-- 啟用/停用
ENABLE TRIGGER tgr_name ON table_name;
DISABLE TRIGGER tgr_name ON table_name;
-- 查看定義
EXEC sp_helptext 'tgr_name';
SELECT * FROM sys.triggers WHERE name = 'tgr_name';
SELECT * FROM sys.trigger_events WHERE object_id = OBJECT_ID('tgr_name');
效能與避坑提醒
- 觸發器在同一交易內執行，邏輯複雜會拉長鎖定時間，影響並發。
- 避免在觸發器發送郵件、呼叫 Web Service 等外部 I/O。
- 大量批次操作（
BULK INSERT、TRUNCATE）不啟動 觸發器。
- 巢狀觸發器預設開啟（
sp_configure 'nested triggers'），慎用避免無限遞迴。
