萬盛學電腦網

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

SQL Server 觸發器實戰詳解:從入門到進階應用

SQL Server 觸發器實戰詳解:從入門到進階應用

觸發器是隨表數據變更(INSERT/UPDATE/DELETE)自動執行的特殊存儲過程。本文系統講解觸發器類型、魔術表、創建語法、實戰案例(級聯操作、列級校驗、日誌記錄)及管理操作,助你掌握這項強大的數據完整性利器。

核心概念

  • 觸發時機:事件驅動自動執行,非手動調用。
  • 分類:
    • DML 觸發器:AFTER(操作後) / INSTEAD OF(替代操作)
    • DDL 觸發器:響應 CREATE/ALTER/DROP 等結構變更(SQL Server 2005+)
  • 魔術表:
    • inserted:新增/修改後的數據
    • deleted:刪除/修改前的數據
    • 結構與基表一致,只讀,內存駐留,觸發結束自動銷毀
  • 事務特性:觸發器隸屬觸發它的事務,可用 ROLLBACK 撤銷操作。

實戰案例庫

1. INSERT 觸發器 — 級聯新增學生信息

CREATE TRIGGER tgr_classes_insert ON classes FOR INSERT AS
DECLARE @id INT, @name VARCHAR(20), @temp INT;
SELECT @id = id, @name = name FROM inserted;
SET @name = @name + CAST(@id AS VARCHAR);
SET @temp = @id / 2;
INSERT INTO student VALUES (@name, 18 + @id, @temp, @id);
PRINT '添加學生成功!';

2. DELETE 觸發器 — 刪除數據自動備份

CREATE TRIGGER tgr_classes_delete ON classes FOR DELETE AS
PRINT '備份數據中……';
IF OBJECT_ID('classesBackup', 'U') IS NOT NULL
    INSERT INTO classesBackup SELECT name, createDate FROM deleted;
ELSE
    SELECT * INTO classesBackup FROM deleted;
PRINT '備份數據成功!';

3. UPDATE 觸發器 — 級聯更新學生表班級名

CREATE TRIGGER tgr_classes_update ON classes FOR UPDATE AS
DECLARE @oldName VARCHAR(20), @newName VARCHAR(20);
SELECT @oldName = name FROM deleted;
IF EXISTS (SELECT * FROM student WHERE name LIKE '%' + @oldName + '%')
BEGIN
    SELECT @newName = name FROM inserted;
    UPDATE student SET name = REPLACE(name, @oldName, @newName)
    WHERE name LIKE '%' + @oldName + '%';
    PRINT '級聯修改成功!';
END
ELSE PRINT '無需修改 student 表!';

4. 列級 UPDATE 觸發器 — 禁止修改創建時間

CREATE TRIGGER tgr_classes_update_column ON classes FOR UPDATE AS
IF UPDATE(createDate)
BEGIN
    RAISERROR('系統提示:班級創建時間不能修改!', 16, 11);
    ROLLBACK TRAN;
END

5. INSTEAD OF 觸發器 — 刪除時級聯清理關聯數據

CREATE TRIGGER tgr_classes_inteadOf 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 '刪除 [id:' + CAST(@id AS VARCHAR) + ', name:' + @name + '] 成功!';

6. 自定義錯誤消息 — RAISERROR

CREATE TRIGGER tgr_message ON student AFTER INSERT, UPDATE AS
RAISERROR('tgr_message 觸發器被觸發', 16, 10);

7. 數據驗證 — 年齡必須 ≥ 18 歲

CREATE TRIGGER tgr_valid_data ON student AFTER INSERT AS
DECLARE @age INT, @name VARCHAR(20);
SELECT @name = s.name, @age = s.age FROM inserted s;
IF @age < 18
BEGIN
    RAISERROR('插入數據年齡不合規', 16, 1);
    ROLLBACK TRAN;
END

8. 操作日誌記錄 — 統一記錄增刪改

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 IF EXISTS (SELECT 1 FROM deleted)
    INSERT INTO log(action) VALUES('deleted');

管理操作速查

-- 修改觸發器
ALTER TRIGGER tgr_message ON student AFTER DELETE AS RAISERROR('...', 16, 10);
-- 禁用/啟用
DISABLE TRIGGER tgr_message ON student;
ENABLE TRIGGER tgr_message ON student;
-- 查詢觸發器信息
SELECT * FROM sys.triggers;
SELECT * FROM sys.objects WHERE type = 'TR';
-- 查看觸發事件
SELECT te.* FROM sys.trigger_events te JOIN sys.triggers t ON t.object_id = te.object_id
WHERE t.parent_class = 0 AND t.name = 'tgr_valid_data';
-- 查看創建腳本
EXEC sp_helptext 'tgr_message';

最佳實踐建議

  • 謹慎使用:觸發器隱式執行,增加除錯難度,優先考慮約束/存儲過程/應用層邏輯。
  • 保持簡單:單一觸發器職責單一,避免複雜業務邏輯耦合。
  • 效能注意:避免觸發器中執行耗時操作(郵件、跨庫查詢),必要時異步處理。
  • 文檔化:在數據庫字典中記錄觸發器用途、觸發條件、副作用。
copyright © 萬盛學電腦網 all rights reserved