Quoth Jim Mellander <jmellan...@lbl.gov>, on 2010-10-01 16:04:57 -0700: > 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 ?
(Note that I'm not sure the above is the best way for various values of "best", just a reasonably simple way that does what you said.) The WHEN and the FOR EACH ROW are separate. The WHEN clause causes the trigger to only run for some rows rather than for all rows, so the body does not execute unless the NOT EXISTS ... part is true. The FOR EACH ROW applies to the trigger as a whole, and indicates how many invocations are generated: one trigger activation per relevant row, with the WHEN clause limiting which rows are considered relevant. In SQLite, the FOR EACH ROW is a syntactic salt no-op, because all triggers act that way, but some database engines also support FOR EACH STATEMENT triggers, which run only once when triggered by a statement that alters multiple rows. I prefer to use the explicit form, but omitting it would change nothing. The full CREATE TRIGGER syntax is of course part of the documentation, at http://sqlite.org/lang_createtrigger.html. ---> Drake Wilson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
