On Wed, Sep 22, 2010 at 11:15 PM, Igor Tandetnik <itandet...@mvps.org>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.
>
> You could do something like
>
> delete from Recipient where recipient_ID = old.recipient_ID and
> recipient_ID not in (select recipient_ID from MessageRecipient);
>
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);
>
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
