觸發器是針對 INSERT、UPDATE、DELETE 自動執行的特殊存儲過程。兩張邏輯表:
UPDATE 視為先 DELETE (進 deleted) 再 INSERT (進 inserted)。
USE Northwind; CREATE TRIGGER OrdDet_Insert ON [Order Details] FOR INSERT AS UPDATE P SET UnitsInStock = P.UnitsInStock - I.Quantity FROM Products P INNER JOIN Inserted I ON P.ProductID = I.ProductID;
CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Products P INNER JOIN Deleted D ON P.CategoryID = D.CategoryID;
注意:TRUNCATE TABLE 不觸發 DELETE 觸發器 (不寫日誌)。
CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS
IF UPDATE(EmployeeID)
BEGIN
RAISERROR('Transaction cannot be processed. Employee ID cannot be modified.', 10, 1);
ROLLBACK TRANSACTION;
END;
IF UPDATE(column) 精準攔截特定欄位變更。
適用於不可直接更新的視圖 (如聯接視圖)。每表/視圖每種動作僅能有一個 INSTEAD OF 觸發器。
-- 建立德/墨兩國客戶表
SELECT * INTO CustomersGer FROM Customers WHERE Country = 'Germany';
SELECT * INTO CustomersMex FROM Customers WHERE Country = 'Mexico';
GO
CREATE VIEW CustomersView AS SELECT * FROM CustomersGer UNION SELECT * FROM CustomersMex;
GO
CREATE TRIGGER Customers_Update2 ON CustomersView INSTEAD OF UPDATE AS
DECLARE @Country NVARCHAR(15);
SET @Country = (SELECT Country FROM Inserted);
IF @Country = 'Germany'
UPDATE CustomersGer SET Phone = Inserted.Phone
FROM CustomersGer JOIN Inserted ON CustomersGer.CustomerID = Inserted.CustomerID;
ELSE IF @Country = 'Mexico'
UPDATE CustomersMex SET Phone = Inserted.Phone
FROM CustomersMex JOIN Inserted ON CustomersMex.CustomerID = Inserted.CustomerID;
測試:UPDATE CustomersView SET Phone='030-007xxxx' WHERE CustomerID='ALFKI' 實際更新 CustomersGer。
CREATE TRIGGER [calT1T2T3] ON dbo.DCLB FOR INSERT, UPDATE AS
UPDATE P SET
P.T1 = (I.P1+I.P2+I.P3+I.P4+I.P5+I.P6),
P.T2 = (I.Y1+I.Y2+I.Y3+I.Y4+I.Y5+I.Y6),
P.T3 = SQRT(P.T1*P.T1 + P.T2*P.T2)
FROM DCLB P INNER JOIN Inserted I ON P.SID = I.SID;
sp_configure 'nested triggers', 0 關閉。WITH CHECK OPTION 視圖上。INSERTED/DELETED 進行集合式操作,避免逐行游標處理。