Sreedhar.a wrote:
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;"
You need to use a conditional delete in your trigger. You can't do that
using a select with a case statement. You could try something like this
(untested):
CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW
BEGIN
delete from Artist
where ArtistId = old.ArtistId
and not exist (select id from music where ArtistId = old.ArtistId);
delete from Album
where AlbumId = old.AlbumId
and not exist (select id from music where AlbumId = old.AlbumId);
delete from BGM
where BgmId = old.BgmId
and not exist (select id from music where BgmId = old.BgmId);
END;"
A couple of other things to note:
You should probably change your table definitions to key the phrase
"INTEGER PRIMARY KEY" together so that sqlite can use its btree key
optimization. Instead of this:
CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text);
use this:
CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text);
Also, if you want to speed up the searches in the deletes trigger above,
at the expense of slowing down all the insert and delete operations into
the tables, you could add indexes on the individual Id columns in the
music table.
create index MusicArtistId on Muisc(ArtistId);
create index MusicAlbumId on Muisc(AlbumId);
create index MusicBgmId on Muisc(BgmId);
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
