您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 與 MySQL 觸發器語法差異深度解析

編輯：MySQL 資料庫相關知識

測試資料表結構

-- 分數表
CREATE TABLE sc (
    ScId INT NOT NULL PRIMARY KEY,
    SNo INT NOT NULL,
    CNo INT NOT NULL,
    Score INT DEFAULT NULL
);

-- 異動記錄表
CREATE TABLE score_record (
    SrId INT NOT NULL PRIMARY KEY,
    SNo INT NOT NULL,
    CNo INT NOT NULL,
    OldScore INT NOT NULL,
    NewScore INT NOT NULL,
    UpdateTime DATETIME NOT NULL
);

MySQL 觸發器寫法

使用 NEW/OLD 伪表欄位，DELIMITER 變更語句結束符：

DROP TRIGGER IF EXISTS `trigger_sr`;
DELIMITER //
CREATE TRIGGER `trigger_sr` AFTER UPDATE ON `sc`
FOR EACH ROW
BEGIN
    INSERT INTO score_record
    SET SNo = NEW.SNo,
        CNo = NEW.CNo,
        OldScore = OLD.Score,
        NewScore = NEW.Score,
        UpdateTime = NOW();
END//
DELIMITER ;

SQL Server 觸發器寫法

使用 INSERTED/DELETED 邏輯表，採 T-SQL 變數宣告與流程控制：

CREATE TRIGGER trigger_sr ON sc
FOR UPDATE
AS
DECLARE @SNo INT, @CNo INT, @OldScore INT, @NewScore INT;
SELECT @SNo = SNo, @CNo = CNo, @OldScore = Score FROM DELETED;
SELECT @NewScore = Score FROM INSERTED;
INSERT INTO score_record (SNo, CNo, OldScore, NewScore, UpdateTime)
VALUES (@SNo, @CNo, @OldScore, @NewScore, GETDATE());

核心差異對照表

特性	MySQL	SQL Server
觸發時機關鍵字	BEFORE / AFTER	INSTEAD OF / AFTER (FOR)
行級觸發	FOR EACH ROW (預設)	隱含行級，透過 INSERTED/DELETED 處理集合
新/舊值引用	NEW.col / OLD.col	INSERTED.col / DELETED.col
語句結束符	需自訂 DELIMITER	標準 GO 或分號
變數宣告	SET @var = ... 或 DECLARE 在 BEGIN 內	DECLARE @var TYPE
多行邏輯區塊	BEGIN ... END	BEGIN ... END
取得當前時間	NOW()	GETDATE()