Dennis wrote:
I think you need to add a delete trigger on the music that will delete
records in the album, artist, and bgm tables if there are no other records
with the same albumId, artitId, or bgmId in the music table (i.e. if this
is the last record in the music table that references a record in each of
the other tables).
Hi,
For the below tables,
CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); CREATE
TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text); CREATE
TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text);
CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER
CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE CASCADE,Artist_Id
INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON
DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES
BGM(BgmId)ON DELETE CASCADE );
I created a trigger as follows .
Here I am checking after deleting that record in Music Table I will check
wheather that Artist_id is present now in that table MUSIC i.e, (SELECT
Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id).And if that
Artist_Id is Null then I will delete it in the ARTIST table.
But this is not happening with the below trigger.
Do I need to add more constraints in the below trigger.
Please help to solve this.
"CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW
BEGIN
SELECT CASE
WHEN
(SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id)
IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id'
END;
END;"
-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 11:53 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote:
>
>
> I have only one ALBUM named 'Confession' with id 1 and
>
> only one ARTIST named 'Madonna' with id 2 and
>
> one BGM named 'rock' with id 5.
>
> MUSIC table will have all these details.
>
>
>
> Now If I delete the Album 'Confession' From table ALBUM.
>
> Then it is deleting that record with that id i.e,1 for 'confession' in
> MUSIC table.So now there is no records in the MUSIC table.
>
> But it is not deleting in the ARTIST and the BGM table since that
> Artist
'
> Madonna ' and BGM 'rock' are present in one record and that record too
> deleted in Music table. For me the complete record details to be
> deleted in all the tables.
>
>
>
> I think I have to add some more constraints in triggers/sql statemets
>
>
I think you need to add a delete trigger on the music that will delete
records in the album, artist, and bgm tables if there are no other records
with the same albumId, artitId, or bgmId in the music table (i.e. if this
is the last record in the music table that references a record in each of
the other tables).
HTH
Dennis Cote
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
