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

SQL Server 觸發器完整指南：DML/INSTEAD OF/列級/遞迴/巢狀與實戰範例

編輯：關於SqlServer

觸發器核心觀念

特殊儲存程序，**由事件自動觸發** (非显式呼叫)
依附於**資料表**或**檢視表**
執行於**同一交易**內，可透過 ROLLBACK 取消觸發動作
兩張虛擬資料表：**inserted** (新/修改後資料)、**deleted** (舊/刪除資料)，唯讀、記憶體駐留

觸發器分類

類別	觸發時機	適用物件	特點
AFTER (FOR)	動作完成後	Table	可多個、可指定順序 (sp_settriggerorder)
INSTEAD OF	取代原動作	Table / View	每表每事件僅一個、可更新不可更新 View
DDL	CREATE/ALTER/DROP 等	Database / Server	SQL 2005+，審計、防護結構變更

虛擬表資料對應

動作	inserted	deleted
INSERT	新增列	空
DELETE	空	被刪除列
UPDATE	修改後列	修改前列

實戰範例集

1. AFTER INSERT：聯動新增


CREATE TRIGGER tgr_Classes_Insert ON Classes
AFTER INSERT
AS
BEGIN
    DECLARE @id INT, @name VARCHAR(20);
    SELECT @id = id, @name = name FROM inserted;
    INSERT INTO Student (Name, Age, ClassID) 
    VALUES (@name + CAST(@id AS VARCHAR), 18 + @id, @id);
    PRINT '學生聯動新增成功';
END

2. AFTER DELETE：刪除備份


CREATE TRIGGER tgr_Classes_Delete ON Classes
AFTER DELETE
AS
BEGIN
    IF OBJECT_ID('ClassesBackup', 'U') IS NOT NULL
        INSERT INTO ClassesBackup SELECT name, createDate FROM deleted;
    ELSE
        SELECT * INTO ClassesBackup FROM deleted;
    PRINT '刪除資料已備份';
END

3. AFTER UPDATE：級聯更新


CREATE TRIGGER tgr_Classes_Update ON Classes
AFTER UPDATE
AS
BEGIN
    DECLARE @oldName VARCHAR(20), @newName VARCHAR(20);
    SELECT @oldName = name FROM deleted;
    SELECT @newName = name FROM inserted;
    IF EXISTS (SELECT 1 FROM Student WHERE Name LIKE '%' + @oldName + '%')
    BEGIN
        UPDATE Student SET Name = REPLACE(Name, @oldName, @newName)
        WHERE Name LIKE '%' + @oldName + '%';
        PRINT '級聯修改完成';
    END
END

4. 列級觸發：防護特定欄位修改


CREATE TRIGGER tgr_Classes_Update_Column ON Classes
AFTER UPDATE
AS
BEGIN
    IF UPDATE(createDate)  -- 偵測 createDate 是否在 SET 子句中
    BEGIN
        RAISERROR('建立日期不可修改', 16, 1);
        ROLLBACK TRANSACTION;
    END
END

5. INSTEAD OF DELETE：級聯刪除 (含子表)


CREATE TRIGGER tgr_Classes_InsteadOf_Delete ON Classes
INSTEAD OF DELETE
AS
BEGIN
    DECLARE @id INT, @name VARCHAR(20);
    SELECT @id = id, @name = name FROM deleted;
    DELETE FROM Student WHERE ClassID = @id;  -- 先刪子表
    DELETE FROM Classes WHERE id = @id;       -- 再刪主表
    PRINT '主從表級聯刪除完成';
END

6. 多事件合一 + 自訂訊息


CREATE TRIGGER tgr_Student_Log ON Student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @action VARCHAR(10);
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        SET @action = 'UPDATED';
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        SET @action = 'INSERTED';
    ELSE
        SET @action = 'DELETED';
    INSERT INTO LogTable (Action, LogTime) VALUES (@action, GETDATE());
END

7. 資料驗證 + 回滾


CREATE TRIGGER tgr_Student_ValidAge ON Student
AFTER INSERT
AS
BEGIN
    DECLARE @age INT;
    SELECT @age = Age FROM inserted;
    IF @age < 18
    BEGIN
        RAISERROR('年齡必須 >= 18', 16, 1);
        ROLLBACK TRANSACTION;
    END
END

管理與維護

操作	T-SQL
停用	DISABLE TRIGGER tgr_Name ON TableName;
啟用	ENABLE TRIGGER tgr_Name ON TableName;
修改	ALTER TRIGGER ...
查看定義	EXEC sp_helptext 'tgr_Name';
列出所有	SELECT * FROM sys.triggers;
查看觸發事件	SELECT * FROM sys.trigger_events te JOIN sys.triggers t ON t.object_id = te.object_id WHERE t.name = 'tgr_Name';