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.
Thank you in advance,
Dennis
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
