萬盛學電腦網

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

SQL Server 觸發器完整操作指南:建立、修改、刪除、查看與實戰範例

觸發器核心特性

  • 特殊預存程序,不可顯式呼叫,由 DML 動作自動觸發。
  • 系統自動維護 Inserted(新資料)與 Deleted(舊資料)兩張虛擬表,結構與基礎表相同,存在記憶體中。
  • AFTER 觸發器:動作後執行,約束檢查先於觸發器,僅限表。
    INSTEAD OF 觸發器:取代動作,可用於檢視表,約束檢查前執行。
  • 每表每種動作僅能有一個 INSTEAD OF 觸發器,但可有多個 AFTER 觸發器。

T-SQL 基本語法

-- 建立
CREATE TRIGGER trigger_name
ON {table_name | view_name}
{FOR | AFTER | INSTEAD OF}
[INSERT, UPDATE, DELETE]
AS
sql_statement;

-- 修改
ALTER TRIGGER trigger_name
ON {table_name | view_name}
{FOR | AFTER | INSTEAD OF}
[INSERT, UPDATE, DELETE]
AS
sql_statement;

-- 刪除
DROP TRIGGER trigger_name;

-- 查看所有觸發器
SELECT * FROM sysobjects WHERE xtype = 'TR';

-- 查看單一觸發器定義
EXEC sp_helptext 'trigger_name';

實戰範例六則

1. 檢查貨品狀態,阻擋異常訂單

CREATE TRIGGER orderinsert ON orders
AFTER INSERT
AS
IF (SELECT status FROM goods g INNER JOIN inserted i ON g.name = i.goodsname) = 1
BEGIN
    PRINT '貨品正在整理中';
    PRINT '訂單無法提交';
    ROLLBACK TRANSACTION;
END;

2. 扣減庫存

CREATE TRIGGER orderinsert1 ON orders
AFTER INSERT
AS
UPDATE g SET storage = storage - i.quantity
FROM goods g INNER JOIN inserted i ON g.name = i.goodsname;

3. 級聯刪除訂單

CREATE TRIGGER goodsdelete ON goods
AFTER DELETE
AS
DELETE FROM orders WHERE goodsname IN (SELECT name FROM deleted);

4. 禁止手動修改訂單日期

CREATE TRIGGER orderdateupdate ON orders
AFTER UPDATE
AS
IF UPDATE(orderdate)
BEGIN
    RAISERROR('orderdate cannot be modified', 10, 1);
    ROLLBACK TRANSACTION;
END;

5. 確保訂單貨品必須存在於貨品主檔

CREATE TRIGGER orderinsert3 ON orders
AFTER INSERT
AS
IF (SELECT COUNT(*) FROM goods g INNER JOIN inserted i ON g.name = i.goodsname) = 0
BEGIN
    PRINT '貨品主檔無此商品';
    ROLLBACK TRANSACTION;
END;

6. 同步寫入另一張表(示範 ALTER TRIGGER)

ALTER TRIGGER addOrder ON Orders
FOR INSERT
AS
INSERT INTO OrderLog (Id, goodName, Number)
SELECT Id, goodName, Number FROM inserted;

除錯提示

使用 sys.dm_tran_locks 與 sys.dm_os_waiting_tasks 監控鎖定與阻塞;善用 PRINT 或 RAISERROR 記錄觸發器執行流程。

copyright © 萬盛學電腦網 all rights reserved