I have another question on this, if you don't mind (please excuse my ignorance of SQL). I tried your trigger:
CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) BEGIN DELETE FROM summary WHERE key = OLD.key; END; and it works as advertised, but I would like to understand why. I'm a bit hazy on why the FOR EACH ROW statement works. Does FOR EACH ROW refer to each row that is being removed from 'detail'? Is it that the statements between BEGIN and END run when the condition 'NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key)' is fulfilled ? Once I grok SQL, I'm sure this will all seem like old hat, but I would appreciate any further clarifications. Thanks in advance. On Fri, Oct 1, 2010 at 3:30 PM, Jim Mellander <[email protected]> wrote: > Thanks, this is great - I'll read up on expression syntax and usage. > > On Fri, Oct 1, 2010 at 3:11 PM, Drake Wilson <[email protected]> wrote: >> Quoth Jim Mellander <[email protected]>, on 2010-10-01 14:38:03 -0700: >>> Hi: >>> >>> I want to use to trigger on deletion of a detail record to >>> automatically delete a summary record, if not more detail records >>> exist, something like: >>> >>> CREATE TRIGGER detail_delete AFTER DELETE ON detail >>> BEGIN >>> -- here I don't know syntax >>> IF COUNT(detail records with key) = 0 DELETE summary WHERE >>> key=old.key; >>> END; >> >> SQLite triggers don't use an imperative language with full control >> structures, just basic SQL. However, in this case you can use a WHEN >> clause: >> >> CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail >> FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) >> BEGIN >> DELETE FROM summary WHERE key = OLD.key; >> END; >> >> ---> Drake Wilson >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
