On 23/09/2010 11:52 p.m., Richard Hipp wrote: >> 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. > > DELETE FROM recipient > WHERE recipient_id = old.recipient_id > AND NOT EXISTS(SELECT 1 FROM message_recipient > WHERE recipient.recipient_id= > message_recipient.recipient_id); >
This works perfectly, thanks. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
