I couldn't get your trigger to work properly on deleting an empty album...it still said "cannot be deleted". I ended up modifying it some..dont' know if this is your cause or not but might be worth a try to modify your trigger.
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE albums (id INTEGER PRIMARY KEY ASC, name TEXT, additional TEXT); INSERT INTO "albums" VALUES(1,'Album1','Text1'); INSERT INTO "albums" VALUES(2,'Album2','Text2'); CREATE TABLE songs (id INTEGER PRIMARY KEY ASC, album_fk INTEGER NOT NULL, title TEXT, url TEXT, duration BIGINT NOT NULL); INSERT INTO "songs" VALUES(1,1,'Song1','Url1',1000); INSERT INTO "songs" VALUES(2,1,'Song2','Url2',1000); INSERT INTO "songs" VALUES(3,1,'Song3','Url3',1000); CREATE TRIGGER trigger_on_delete BEFORE DELETE ON albums FOR EACH ROW BEGIN SELECT RAISE(FAIL,'album has songs cannot be deleted') WHERE (SELECT count(album_fk) FROM songs WHERE (album_fk = OLD.id) > 0); END; COMMIT; Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Martin Obreshkov Sent: Fri 7/30/2010 5:46 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] delete constraint Hi all, I have two tables albums and songs and each song has reference to alum's id. Album table : CREATE TABLE albums (id INTEGER PRIMARY KEY ASC, name TEXT, additional TEXT) Song table: CREATE TABLE songs (id INTEGER PRIMARY KEY ASC, album_fk INTEGER NOT NULL, title TEXT, url TEXT, duration BIGINT NOT NULL) and i also have a trigger to check when i delete an album if there are existing songs in it : CREATE TRIGGER trigger_on_delete BEFORE DELETE ON albums FOR EACH ROW BEGIN SELECT RAISE(FAIL,'album has songs cannot be deleted')"); WHERE (SELECT album_fk FROM songs WHERE album_fk = OLD.id) IN NOT NULL;) END I am running al this on Android and it's working fine but sometimes Exception: android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed Stack Trace : android.database.sqlite.SQLiteStatement.native_execute(Native Method) android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java: 66) is thrown when i try to delete an album. My delete method is simply deleting an album by id.The bad thing is that i can't reproduce this so there is no much info i can provide. The only thing i now is this exception. I tried different scenarios with deleting an album but i never got this exception. So any ideas how can investigate this issue. There are not any contraints in album table so what could cause such exception ? Any help will be appreciated. -- When I raise my flashing sword, and my hand takes hold on judgment, I will take vengeance upon mine enemies, and I will repay those who haze me. Oh, Lord, raise me to Thy right hand and count me among Thy saints. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
