2009/9/10 Dennis Volodomanov <denn...@conceiva.com>: > Hello, > > I have one more SQL query question - the people on this list have been very > helpful in the past, so thank you! > > 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 > > If I run just the SELECT statement within the brackets above (supplying 1 > instead of OLD.REFB of course), it seems to produce the correct row number > (1), but if I run the complete DELETE statement, nothing happens.
This is simpler and appears to do what you want: sqlite> create table TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER ); sqlite> create table TABLEB ( ID INTEGER PRIMARY KEY, DATA ); sqlite> insert into tableb values( 1, 'data1' ); sqlite> insert into tableb values( 2, 'data2' ); sqlite> insert into tablea values( 1, 1 ); sqlite> sqlite> select * from tableb; 1|data1 2|data2 sqlite> sqlite> select * from tablea; 1|1 sqlite> sqlite> create trigger UpdateData after update on tablea begin ...> delete from tableb where id=old.refb and id not in ( select refb from tablea ); ...> end; sqlite> sqlite> sqlite> update tablea set refb=2; sqlite> sqlite> sqlite> select * from tableb; 2|data2 sqlite> > > Thank you in advance, > > Dennis Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
