I have three tables
The end-user can select a top2000jaar from a dropdownlist in ASP, which will has its value send to an stored procedure, the stored procedure has to delete all the values in [LIJST] where top2000jaar equals the value that has been selected but a trigger is needed which delete the row where song.songid = lijst.songid from [SONG] when that songid doesnt exist in [LIJST] anymore, and therefore if an artist in [artiest] has no more songs left in [song], the artist should be deleted too.
All that deleted data should be then put in [logtable]
I have tried a way which i thought would work, but didnt.
Expected output should be this:
<- 31-dec-13 is a typo, it is 2013
This is the trigger is created, with some answers i found on google, but not working:
ALTER TRIGGER [dbo].[TRGremoveSong]
ON [dbo].[Lijst]
AFTER DELETE AS
Begin
DELETE FROM song where songid not in (Select songid from lijst)
END
begin
INSERT INTO logtable
SELECT
*
FROM deleted
end
I believe using this as your trigger should do the job, this will only populate what has actually been deleted so if artiest has not been deleted it will show as null next to the song etc.
Here is an example SQLFiddle where song 9 is deleted and it's the last song for that artist: http://sqlfiddle.com/#!6/4405b/1/0
The following will show all fields on deletion:
Here is the SQLFiddle for this: http://sqlfiddle.com/#!6/fd6171/1/0