Hi Dennis,
Thanks a lot for ur help.
Ya.Its deleting properly in all the tables by the following trigger.
"CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW BEGIN
DELETE from ARTIST where ArtistId = old.Artist_Id and not exists (select id
from MUSIC where Artist_Id = old.Artist_Id); DELETE from ALBUM where AlbumId
= old.Album_Id and not exists (select Id from MUSIC where Album_Id =
old.Album_Id); DELETE from BGM where BgmId = old.Bgm_Id and not exists
(select Id from MUSIC where Bgm_Id = old.Bgm_Id); DELETE from PLAYLIST where
TrackId = old.Id and not exists (select Id from MUSIC where Id = old.Id);
END;"
Right now I am trying to INSERT and UPDATE by means of triggers.
My doubt is:
"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album
Text,unique(Album));"
"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,Artist
Text,unique(Artist));"
"CREATE TABLE BGM (BgmId INTEGER PRIMARY KEY NOT NULL,Bgm
Text,unique(Bgm));"
"CREATE TABLE PLAYLIST(PlayListName Text,TrackId INTEGER);"
"CREATE TABLE MUSIC (Id INTEGER PRIMARY KEY NOT NULL,Album_Id
INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER,Track text);"
Now For insertion,
1.Do I want to insert in to ALBUM table first or I can directly insert into
MUSIC table.If I am entering into MUSIC table I wont get the AlbumId from
ALBUM table since it is not inserted.Similar for ARTIST and BGM table.
By using Triggers is it possible to insert in to all tables .In
ALBUM,ARTIST,BGM tables only unique values of Album,Artist,Bgm should be
present.i.e,if I am entering a record in MUSIC table and the Album_Id is
already present in ALBUM table then I wont insert that Album in ALBUM table.
I tried with the following and its working.
"insert or ignore into ALBUM (Album) values('Album3');"
"insert or ignore into ARTIST (Artist) values('Artist1');"
"insert or ignore into BGM (Bgm) values('Rock');"
"insert into MUSIC (Album_Id,Artist_Id,Bgm_Id,Track) values((select AlbumId
from ALBUM where Album='Album3'),(select ArtistId from ARTIST where
Artist='Artist1'),(select BgmId from BGM where Bgm='Rock'),'Track1.mp3');"
I want to know weather the above is better than TRIGGERS.
If we can do this by INSERT TRIGGERS how the statement looks like [For both
insert and update].
Even if update any records in one table It has to be changed in all the
others Please help to solve this.
Thanks & Regards,
Sreedhar.A
-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 04, 2007 9:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite:Deletion in Joins method
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]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
