萬盛學電腦網

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

SQL Server 2000 觸發器類型詳解與實戰應用範例

觸發器核心概念

觸發器是針對 INSERT、UPDATE、DELETE 自動執行的特殊存儲過程。兩張邏輯表:

  • inserted:新增/修改後的資料映像 (after image)。
  • deleted:刪除/修改前的資料映像 (before image)。

UPDATE 視為先 DELETE (進 deleted) 再 INSERT (進 inserted)。

四大觸發器類型與範例

1. INSERT 觸發器 —— 庫存自動扣減

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;

2. DELETE 觸發器 —— 級聯標記停產

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 觸發器 (不寫日誌)。

3. UPDATE 觸發器 —— 禁止修改主鍵

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) 精準攔截特定欄位變更。

4. INSTEAD OF 觸發器 —— 視圖更新重導向

適用於不可直接更新的視圖 (如聯接視圖)。每表/視圖每種動作僅能有一個 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;

最佳實踐提醒

  • 觸發器內避免複雜業務邏輯,影響 DML 性能。
  • 謹慎使用巢狀觸發器 (巢狀層級 ≤ 32),可用 sp_configure 'nested triggers', 0 關閉。
  • INSTEAD OF 觸發器不可建立於 WITH CHECK OPTION 視圖上。
  • 善用 INSERTED/DELETED 進行集合式操作,避免逐行游標處理。
copyright © 萬盛學電腦網 all rights reserved