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

SQL Server 觸發器完整指南：類型、語法、實戰範例與進階應用

編輯：關於SqlServer

觸發器是針對資料表的 INSERT、UPDATE、DELETE 操作自動執行的特殊預存程序，常用於實作複雜的業務規則、跨表級聯更新、稽核追蹤等 CHECK 約束無法涵蓋的場景。本文系統整理 SQL Server 觸發器的核心概念、分類、語法與完整實戰範例。

核心特性

自動觸發： 無法直接呼叫，僅由對應 DML/DDL 動作啟動。
交易本質： 觸發器內發生錯誤可觸發回滾，確保資料一致性。
虛擬資料表： 系統自動提供 inserted（新資料）與 deleted（舊資料）兩張唯讀暫存表。
跨表參考： 可在觸發器中查詢、修改其他資料表，突破 CHECK 約束限制。

分類概覽

大類	細分類型	觸發時機
DML 觸發器	AFTER INSERT	插入完成後
	AFTER UPDATE	更新完成後
	AFTER DELETE	刪除完成後
DDL 觸發器	CREATE / ALTER / DROP	結構定義語句執行後
INSTEAD OF 觸發器	INSERT / UPDATE / DELETE	取代原本動作，可用於檢視表

基礎建立語法

CREATE TRIGGER trigger_name 
ON table_name 
[WITH ENCRYPTION] 
FOR [INSERT, UPDATE, DELETE] 
AS 
    T-SQL 陳述式
GO

實戰範例：網咖上機記錄系統

假設情境：recordInfo（上機記錄）、PCInfo（電腦狀態）、CardInfo（會員卡）。

1. AFTER INSERT 觸發器 — 上機自動更新電腦狀態

CREATE TRIGGER tr_insert_recordInfo 
ON recordInfo 
FOR INSERT 
AS 
BEGIN
    DECLARE @PCId INT, @CardId CHAR(10), @CardNumber CHAR(20);
    
    SELECT @PCId = PCId, @CardId = CardId FROM inserted;
    
    UPDATE PCInfo SET PCUse = 1 WHERE PCId = @PCId;  -- 標記電腦使用中
    
    SELECT @CardNumber = CardNumber FROM CardInfo WHERE CardId = @CardId;
    PRINT '上機成功！會員號：' + @CardNumber + ' 機器號：' + CAST(@PCId AS VARCHAR);
END
GO

2. AFTER UPDATE 觸發器 — 換機邏輯

CREATE TRIGGER tr_update_recordInfo 
ON recordInfo 
FOR UPDATE 
AS 
BEGIN
    DECLARE @BeforePCId INT, @AfterPCId INT;
    SELECT @BeforePCId = PCId FROM deleted;
    SELECT @AfterPCId = PCId FROM inserted;
    
    UPDATE PCInfo SET PCUse = 0 WHERE PCId = @BeforePCId;  -- 原電腦釋放
    UPDATE PCInfo SET PCUse = 1 WHERE PCId = @AfterPCId;  -- 新電腦佔用
    
    PRINT '換機成功：從 ' + CAST(@BeforePCId AS VARCHAR) + ' 號換到 ' + CAST(@AfterPCId AS VARCHAR) + ' 號';
END
GO

3. AFTER DELETE 觸發器 — 刪除備份

CREATE TRIGGER tr_delete_recordInfo 
ON recordInfo 
FOR DELETE 
AS 
BEGIN
    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'backRecordInfo')
        INSERT INTO backRecordInfo SELECT * FROM deleted;
    ELSE
        SELECT * INTO backRecordInfo FROM deleted;
    
    PRINT 'backRecordInfo 備份完成';
END
GO

4. INSTEAD OF INSERT 觸發器 — 餘額檢核與條件攔截

CREATE TRIGGER tr_instead_insert_recordInfo 
ON recordInfo 
INSTEAD OF INSERT 
AS 
BEGIN
    DECLARE @CardId INT, @PCId INT, @Balance INT;
    SELECT @CardId = CardId, @PCId = PCId FROM inserted;
    SELECT @Balance = CardBalance FROM CardInfo WHERE CardId = @CardId;
    
    IF @Balance < 2
    BEGIN
        PRINT '餘額不足 2 元，無法上機，請儲值！';
        RETURN;  -- 直接結束，不執行原 INSERT
    END
    
    UPDATE PCInfo SET PCUse = 1 WHERE PCId = @PCId;
    INSERT INTO recordInfo (CardId, PCId, BeginTime) VALUES (@CardId, @PCId, GETDATE());
    PRINT '上機成功';
END
GO