如何创建含归档表的SQL删除触发器，同步归档关联记录？

要实现删除Songs表记录时同步归档并删除所有关联表记录，需要分两步调整：扩展归档表结构和修改触发器逻辑。

1. 创建关联表对应的归档表 #

原Archives表仅存储Songs数据，需新增归档表保存关联表的历史记录：

-- 归档SongPerformers关联数据
CREATE TABLE Archives_SongPerformers
(
    Id INT PRIMARY KEY NOT NULL IDENTITY,
    SongID INT NOT NULL,
    PerformerID INT NOT NULL,
    ArchiveDate DATETIME NOT NULL
)

-- 归档SongGenres关联数据
CREATE TABLE Archives_SongGenres
(
    Id INT PRIMARY KEY NOT NULL IDENTITY,
    SongID INT NOT NULL,
    GenreID INT NOT NULL,
    ArchiveDate DATETIME NOT NULL
)

-- 归档SongDirectors关联数据
CREATE TABLE Archives_SongDirectors
(
    Id INT PRIMARY KEY NOT NULL IDENTITY,
    SongID INT NOT NULL,
    DirectorID INT NOT NULL,
    ArchiveDate DATETIME NOT NULL
)

2. 修改触发器为INSTEAD OF DELETE #

使用INSTEAD OF DELETE触发器可以避免外键约束导致的删除失败，先处理关联表的归档与删除，再操作主表：

ALTER TRIGGER Archivetrigger
ON Songs
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON; -- 屏蔽多余的影响行数返回信息

    -- 1. 归档Songs主表待删除记录
    INSERT INTO Archives (SongTitle, SongReleaseDate, SongTime, SongLanguage, [Date])
    SELECT d.SongTitle, d.SongReleaseDate, d.SongTime, d.SongLanguage, GETDATE()
    FROM deleted d

    -- 2. 归档并删除SongPerformers关联记录
    INSERT INTO Archives_SongPerformers (SongID, PerformerID, ArchiveDate)
    SELECT sp.SongID, sp.PerformerID, GETDATE()
    FROM SongPerformers sp
    JOIN deleted d ON sp.SongID = d.SongID

    DELETE FROM SongPerformers
    WHERE SongID IN (SELECT SongID FROM deleted)

    -- 3. 归档并删除SongGenres关联记录
    INSERT INTO Archives_SongGenres (SongID, GenreID, ArchiveDate)
    SELECT sg.SongID, sg.GenreID, GETDATE()
    FROM SongGenres sg
    JOIN deleted d ON sg.SongID = d.SongID

    DELETE FROM SongGenres
    WHERE SongID IN (SELECT SongID FROM deleted)

    -- 4. 归档并删除SongDirectors关联记录
    INSERT INTO Archives_SongDirectors (SongID, DirectorID, ArchiveDate)
    SELECT sd.SongID, sd.DirectorID, GETDATE()
    FROM SongDirectors sd
    JOIN deleted d ON sd.SongID = d.SongID

    DELETE FROM SongDirectors
    WHERE SongID IN (SELECT SongID FROM deleted)

    -- 5. 最后删除Songs主表记录
    DELETE FROM Songs
    WHERE SongID IN (SELECT SongID FROM deleted)
END

补充说明 #

• 若你的关联表外键已设置ON DELETE CASCADE，可跳过触发器中的DELETE FROM 关联表语句，仅保留归档逻辑即可。
• 测试时执行原删除语句DELETE FROM Songs WHERE SongID = 1，即可同步完成所有归档与删除操作。

内容的提问来源于stack exchange，提问作者user15004061