从3个表中获取已删除的值并放入1个表中

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.

最终用户可以从ASP中的下拉列表中选择top2000jaar,其值将发送到存储过程,存储过程必须删除[LIJST]中的所有值,其中top2000jaar等于已选择但触发的值需要删除[SONG]中song.songid = lijst.songid的行,当[LIJST]中不再存在该歌曲时,因此如果[artiest]中的艺术家在[song]中没有剩余的歌曲,则该艺术家也应该删除。

All that deleted data should be then put in [logtable]

所有删除的数据应该放在[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

< - 31-dec-13是一个错字,是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

2 个解决方案

#1

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.

我相信使用它作为你的触发器应该完成这项工作,这只会填充实际被删除的内容,所以如果没有删除它,它将在歌曲旁边显示为null等。

ALTER TRIGGER [dbo].[TRGremoveSong]
ON [dbo].[Lijst]
AFTER DELETE AS

Begin
   INSERT INTO logtable
   SELECT  sng.titel, art.naam, sng.jaar, positie, top2000jaar
   FROM deleted d
   Outer Apply
   (Select titel, jaar, artiestid from song where not exists (Select * from lijst where songid = d.songid) and songid = d.songid) sng
   Outer Apply
   (Select naam from artiest where not exists (Select * from song where artiestid = sng.artiestid and songid <> d.songid) and artiestid = sng.artiestid) art

DELETE FROM song s where not exists (Select * from lijst where songid = s.songid)
DELETE FROM artiest a where not exists (Select * from song where artiestid = a.artiestid)
End

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

这是一个示例SQLFiddle,其中歌曲9被删除,这是该艺术家的最后一首歌:http://sqlfiddle.com/#!6/4405b/1/0

The following will show all fields on deletion:

以下将显示删除的所有字段:

ALTER TRIGGER [dbo].[TRGremoveSong]
ON [dbo].[Lijst]
AFTER DELETE AS

Begin
   INSERT INTO logtable
   SELECT  sng.titel, art.naam, sng.jaar, positie, top2000jaar
   FROM deleted d
   Outer Apply
   (Select titel, jaar, artiestid from song where songid = d.songid) sng
   Outer Apply
   (Select naam from artiest where artiestid = sng.artiestid) art

DELETE FROM song s where not exists (Select * from lijst where songid = s.songid)
DELETE FROM artiest a where not exists (Select * from song where artiestid = a.artiestid)
End

Here is the SQLFiddle for this: http://sqlfiddle.com/#!6/fd6171/1/0

这是SQLFiddle:http://sqlfiddle.com/#!6/fd6171/1/0

#2

You could just add the logic to the stored procedure to do all of that, something like this:

您可以将逻辑添加到存储过程来完成所有这些,如下所示:

DECLARE @LIJSTID_TO_DELETE INT = 123
DECLARE @SongID INT
DECLARE @ArtistID INT

-- SET the songid for the row you're going to delete
SELECT @SongID = songid 
FROM LIJST 
WHERE top2000jaar = @LIJSTID_TO_DELETE

-- first deletion
DELETE FROM LIJST 
WHERE top2000jaar = @LIJSTID_TO_DELETE

IF @@ROWCOUNT > 0
    INSERT INTO LOGTABLE.... -- deleted LIJST values

-- set the artist id for the song you are going to delete
SELECT @ArtistID = artiestid
FROM Song 
WHERE songid = @SongID

-- delete the linked song (could use cascade delete if required)
DELETE FROM Song
WHERE songid = @SongID

IF @@ROWCOUNT > 0
    INSERT INTO LOGTABLE.... -- deleted song values

-- Delete the artist if their songs no longer exist
IF NOT EXISTS (SELECT * FROM Song WHERE artiestid = ArtistID )
BEGIN
    DELETE FROM Artiest
    WHERE artiestid = @ArtistID 

    IF @@ROWCOUNT > 0
        INSERT INTO LOGTABLE.... -- deleted artiest values
END

@@ROWCOUNT simply returns the number of rows affected by the previous statement, so it will only log if a row is deleted.

@@ ROWCOUNT只返回受前一个语句影响的行数,因此只有在删除行时才会记录。