Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: > On 05/31/2013 06:32 AM, Kevin Grittner wrote: >> Juliano Amaral Chaves <juliano.amaral@xxxxxxxxxxx> wrote: >> >>> By doing insert into a table using a query, it seems that all >>> records of consultation were included bypassing the AFTER INSERT >>> triggers and as few as after all the records already included, >>> the TRIGGER is fired for each record, I wonder if this occurrence >>> is normal. >>> >>> I created an example that demonstrates my doubt, where the 1st >>> case works properly. The 2nd case is restricted by TRIGGER when I >>> think, that could not be >> >>> [ example showing that an AFTER INSERT trigger sees the modified >>> table as of the state after the INSERT statement completes, >>> rather than the state after the insert of the individual row ] >> >> I can see where that would be confusing, but things are operating >> as designed, AFAICS. The trigger fires after the statement, not >> after each affected row is processed. > > But why? The OP specified FOR EACH ROW in the trigger statement. Hmm. I went to the SQL spec, and the behavior expected by Juliano seems to be what it mandated by the spec. To quote a couple brief snippets from the spec, the trigger should fire "either immediately before the triggering event or immediately after it, according to its specified trigger action time" and "Every trigger event arises as a consequence of executing some SQL-data change statement." It is clear from the spec that a MERGE *statement*, for example, can cause different types of triggers (INSERT and UPDATE, for example) to fire. The distinction between a "triggering event" and the "data change statement" causing the event is pretty explicit. At least, that's how I interpret the spec. That said, it would be a big behavior change to make it work that way. Such a change could only be considered for a major release, and the benefits of conforming to the standard in this respect would need to be balanced against the cost of making the change and the inevitable breakage of currently-working applications which would certainly result. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
