Dennis Volodomanov <[email protected]> wrote: > I'm trying to create a trigger that would delete unreferenced rows > from a table. > > Let's say the schema is like this: > > TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER ); > TABLEB ( ID INTEGER PRIMARY KEY, DATA ); > > And let's say the data is like this: > > TABLEA: > 1|1 > > TABLEB: > 1|dataA > 2|dataB > > Now, I update the record in TABLEA and change REFB to be REFB=2 > > I've got the following trigger, which doesn't work: > > CREATE TRIGGER UpdateData AFTER UPDATE ON TABLEA > BEGIN > DELETE FROM TABLEB WHERE TABLEB.ID IN ( SELECT TABLEB.ID FROM TABLEB > LEFT JOIN TABLEA ON TABLEA.REFB=TABLEB.ID WHERE TABLEB.ID=OLD.REFB > AND TABLEB.ID NOT IN ( TABLEA.REFB ) ); > END
This statement makes no sense to me. Why not simply delete from TABLEB where ID=old.REFB and ID != new.REFB; Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
