CODEWITHSUNDEEP
sqlsql-servertriggerssql-delete
user15004061
user15004061

Reputation:

Creating a delete trigger with archive table for records and all of its related records in SQL

I need to create a deleting trigger with archive table so when I delete a record from one table it goes into archive table plus its related records from other tables in SQL.

I have 4 tables called Songs, Genres, Performers and Directors plus relations which are SongPerformers connecting Songs and Performers, relation SongGenres connecting Songs and Genres and SongDirectors connecting Songs and Directors.

I created the delete trigger for the Songs table and archive table too. The problem is that when deleting from the Songs table, the records that are deleted are just in Songs table but doesn't delete the related records from other tables.

Here is the database diagram of how the database looks and the trigger I created:

Database diagram

Trigger:

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

Like how can I add the other tables in the trigger so when I delete a record from Songs all of its related records are deleted too and added to the archive table.

Upvotes: 0

Views: 1143

Answers (1)

user15004061
user15004061

Reputation:

So i found the answer that works for my case (it's not perfect but it works), all i had to do in the trigger was to add some lines that help delete the related records from the other tables:

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

Here is the syntax, it's obviously not perfect, but its close to what i need and works for me:

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

You can add all the table you need to be deleted from.

Upvotes: 1

Related Questions