Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 05/31/2013 06:32 AM, Kevin Grittner wrote:
>> Juliano Amaral Chaves <juliano.amaral@hotmail.com> 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
