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

MSSQL 觸發器實戰：INSERT、UPDATE、DELETE 觸發與 Inserted/Deleted 魔術表應用

編輯：關於SqlServer

場景

兩張表：studentcourse(學號, 姓名, 課程...) 與 information(學號, 姓名, 性別, 籍貫...)。需求：當 studentcourse 的 姓名 更新時，同步更新 information 表的對應姓名。

更新觸發器範例

CREATE TRIGGER TR_StudentCourse_UpdateName
ON studentcourse
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 判斷是否更新了姓名欄位
    IF UPDATE(姓名)
    BEGIN
        UPDATE i
        SET i.姓名 = d.姓名
        FROM information i
        INNER JOIN deleted d ON i.學號 = d.學號
        INNER JOIN inserted ins ON d.學號 = ins.學號;
    END
END
GO

核心概念：Inserted 與 Deleted 魔術表

判斷觸發類型的通用模式

CREATE TRIGGER TR_All_Actions ON studentcourse
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        PRINT 'UPDATE 操作';
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        PRINT 'INSERT 操作';
    ELSE IF EXISTS (SELECT 1 FROM deleted)
        PRINT 'DELETE 操作';
END

完整範例：同步 INSERT/UPDATE/DELETE

CREATE TRIGGER TR_StudentCourse_Sync
ON studentcourse
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 同步新增
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO information (學號, 姓名, 性別, 籍貫)
        SELECT 學號, 姓名, 性別, 籍貫 FROM inserted;
    END

    -- 同步更新
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        UPDATE i
        SET 姓名 = ins.姓名, 性別 = ins.性別, 籍貫 = ins.籍貫
        FROM information i
        INNER JOIN inserted ins ON i.學號 = ins.學號
        INNER JOIN deleted d ON i.學號 = d.學號;
    END

    -- 同步刪除
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        DELETE FROM information
        WHERE 學號 IN (SELECT 學號 FROM deleted);
    END
END
GO