CODEWITHSUNDEEP
sqlsql-serverloggingtriggers
stefan
stefan

Reputation: 165

Logging rows that are deleted in a table sql server

i have made a topic before, but i think that i have not been really clear, i am going to try to explain it in as much detail as i possibly can.

i have a table called List, this has 32000 rows, 2000 for each year(1999-2014) which has columns: SongID,Top2000Year,Position,

What has to happen:

if someone deletes for example the year 2000, all 2000 songs of the year 2000 will be deleted, theres no problem with that

Delete from List WHERE top2000Year = 2000

But, the problem i am facing is: i need this to be logged, i created a log table called LogTable with the columns: Title,Artist,Year,Position Title will be the song title, year is the year the song came out and position is the position in the list

those are from a different table, i join them like this

select title, name[artist],song.Year[SongYear], position from List
left join Song on Song.songid = List.songid
left join Artist on Artist.artistid = Song.artistid
where top2000Year = 2013
order by position asc

this shows a query like this http://puu.sh/i8f2u/8702ee7010.png (Censored the server, it's my teachers so i figured i'd censor it)

i am trying to fill my LogTable exactly like that, if the year 2013 is deleted, those 2000 rows should be inserted into the LogTable using a trigger

Edit: i used this https://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/ and it worked, then i tried it with my database, and it didn't work.

Code:

USE [TOP2000DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRGDeleteList]
ON list 
FOR DELETE
AS
INSERT INTO List (songid,top2000Year,position)
SELECT songid,top2000Year,position FROM Inserted
INSERT INTO [dbo].[LogTabel] (title,artist,Year,position)
SELECT title,artist,year,position FROM Deleted

Upvotes: 2

Views: 1638

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Just create a trigger on your table for delete action:

CREATE TRIGGER TriggerName
    ON List
    FOR DELETE
AS
    INSERT INTO LogTable (Title, Artist, Year, Position)
    SELECT s.Title, a.Name, s.Year, s.Position 
    FROM DELETED d
    LEFT JOIN Song s ON s.songid = d.songid
    LEFT JOIN Artist a ON a.artistid = s.artistid
GO

You need to join on DELETED virtual table to get all deleted rows.

Upvotes: 1

Related Questions