SQL Server 在SQL中为记录及其所有相关记录创建带有存档表的删除触发器

c3frrgcw 于 2022-12-22 发布在其他

关注(0)|答案(1)|浏览(123)

我需要创建一个删除触发器与档案表，所以当我从一个表中删除一条记录，它进入档案表及其相关的记录从其他表在SQL。
我有4个表，分别叫做Songs、Genres、Performers和Directors，加上关系，即连接Songs和Performers的SongPerformers、连接Songs和Genres的SongGenres以及连接Songs和Directors的SongDirectors。
我也为Songs表和存档表创建了删除触发器，问题是从Songs表中删除时，删除的记录只在Songs表中，而不删除其他表中的相关记录。
下面是数据库图表，显示了数据库的外观和我创建的触发器：
Database diagram
触发器：

CREATE TABLE Archives
(
    Id INT PRIMARY KEY NOT NULL IDENTITY,
    SongTitle VARCHAR (50) NOT NULL,
    SongReleaseDate date NOT NULL,
    SongTime float NOT NULL,
    SongLanguage VARCHAR (50) NOT NULL,
    Date DATETIME NOT NULL
)

CREATE TRIGGER Archivetrigger
ON Songs
AFTER DELETE
AS
    INSERT INTO Archives (SongTitle, SongReleaseDate, SongTime, SongLanguage, [Date])
        SELECT 
            d.SongTitle, d.SongReleaseDate, d.SongTime, d.SongLanguage, GETDATE()
        FROM 
            deleted d


DELETE FROM Songs
WHERE SongID = 1

SELECT * FROM Archives

例如，我如何在触发器中添加其他表，以便在从歌曲中删除记录时，其所有相关记录也将被删除并添加到存档表中。

sql-server

来源：https://stackoverflow.com/questions/74835714/creating-a-delete-trigger-with-archive-table-for-records-and-all-of-its-related

1条答案

按热度按时间

9avjhtql1#

所以我找到了适合我的答案（虽然不完美，但确实有效），我在触发器中所要做的就是添加一些行，以帮助删除其他表中的相关记录：

CREATE TABLE Archives
(
ID INT PRIMARY KEY NOT NULL IDENTITY,
SongTitle VARCHAR (50) NOT NULL,
SongReleaseDate date NOT NULL,
SongTime float NOT NULL,
SongLanguage VARCHAR (50) NOT NULL,
Date DATETIME NOT NULL
)
CREATE TRIGGER Archivetrigger
ON Songs
AFTER delete
AS
BEGIN
INSERT INTO Archives (SongTitle, SongReleaseDate, SongTime, SongLanguage, [Date])
SELECT d.SongTitle, d.SongReleaseDate, d.SongTime,d.SongLanguage,GETDATE()
FROM deleted d
DELETE FROM SongPerformers WHERE SongID = (SELECT TOP 1 SongID FROM deleted)
DELETE FROM SongDirectors WHERE SongID = (SELECT TOP 1 SongID FROM deleted)
DELETE FROM SongGenres WHERE SongID = (SELECT TOP 1 SongID FROM deleted)
END

下面是语法，它显然不是完美的，但它接近我需要的，并为我工作：

CREATE TRIGGER [dbo].[*nameoftrigger*]
ON [dbo].[*nameoftable*]
AFTER DELETE
AS
BEGIN
DELETE FROM *tableyoudeletefrom* WHERE *tableID* = (SELECT TOP 1 *tableID* FROM DELETED)
END

您可以添加所有需要从中删除的表。

赞(0）回复(0）举报 2022-12-22

我来回答

SQL Server 在SQL中为记录及其所有相关记录创建带有存档表的删除触发器

1条答案

相关问题

热门标签

最新问答