Josh Gibbs <jgi...@imailds.com> wrote: > CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, > Subject TEXT); > CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES > Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES > Recipient(recipient_id)); > CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT, > name); > > I've tried creating a trigger after delete on MessageRecipient to remove the > referenced Recipient, and this works if it's the only related item, > however any > other MessageRecipient relationship causes the delete to fail. As there > is no > 'or ignore' for the delete statement, I can't get this to keep my data > clean.
You could do something like delete from Recipient where recipient_ID = old.recipient_ID and recipient_ID not in (select recipient_ID from MessageRecipient); -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users