SQL Server 觸發器實戰全解：INSERTED/DELETED 虛擬表、DML/DDL/INSTEAD OF 觸發器與視圖應用

觸發器基礎範例：同步工程名稱

CREATE TRIGGER TG_ProjectName
ON Table1
AFTER UPDATE
AS
BEGIN
    UPDATE Table2
    SET [工程名] = i.工程名
    FROM Table2 t
    INNER JOIN inserted i ON t.ProjID = i.ID;
END

核心概念：INSERTED 與 DELETED 虛擬表

特性：記憶體駐留、唯讀、觸發器範圍內可用、不可直接修改資料。

觸發器分類與適用場景

AFTER 觸發器進階規則

1. 多列更新檢測：`IF UPDATE(欄位名)` 或 `COLUMNS_UPDATED()` 位元遮罩判斷特定欄位是否被修改。
2. 多列觸發處理：觸發器**一次處理整批列**，不可假設單列。需用 `INSERTED`/`DELETED` 集合操作 (JOIN/EXISTS)，而非標量變數。
3. 遞迴/巢狀觸發：預設允許 (巢狀 32 層)。`sp_configure 'nested triggers', 0` 關閉。遞迴觸發需 `RECURSIVE_TRIGGERS` 資料庫選項開啟。
4. TEXT/NTEXT/IMAGE 限制：SQL 2000 AFTER 觸發器不可引用這三類大物件欄位；INSTEAD OF 可引用。SQL 2005+ 已移除此限制 (型別改為 MAX)。

INSTEAD OF 觸發器：視圖更新的關鍵

視圖含衍生欄位、多表 JOIN、聚合時**不可直接更新**。INSTEAD OF 觸發器接管更新邏輯：

CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS SELECT EmployeeID, LastName, FirstName FROM Employees;

CREATE TRIGGER TR_EmployeeNames_Update
ON EmployeeNames
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Employees
    SET LastName = i.LName, FirstName = i.FName
    FROM Employees e
    INNER JOIN inserted i ON e.EmployeeID = i.EmployeeID;
END

INSERTED/DELETED 結構** 與視圖 SELECT 清單一致，而非基表結構。衍生運算式 (常數、函數) 會被忽略，複雜運算式需觸發器自行處理。

-- 資料庫級：防止誤刪表
CREATE TRIGGER TR_Prevent_Drop_Table
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT '資料庫禁止直接 DROP TABLE，請使用受控部署流程。';
    ROLLBACK;
    -- 可用 EVENTDATA() 取得登入名、物件名、SQL 文本、時間
END

-- 伺服器級：稽核所有 CREATE_LOGIN
CREATE TRIGGER TR_Audit_Create_Login
ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
    INSERT INTO AuditDB.dbo.LoginAudit (EventXML, EventTime)
    VALUES (EVENTDATA(), GETDATE());
END