Re: Insert with query

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On 05/31/2013 08:15 AM, Kevin Grittner wrote:
>> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>>> On 05/31/2013 06:32 AM, Kevin Grittner wrote:

>>> But why? The OP specified FOR EACH ROW in the trigger
>>> statement.
>>
>> I went to the SQL spec,

>> To quote [...], the trigger should fire "either immediately
>> before the triggering event or immediately after it, [...]"

>> The distinction between a "triggering event" and the
>> "data change statement" causing the event is pretty explicit.

>> it would be a big behavior change to make it work that way.

> The thing is I thought it was working to spec and the docs would
> seem to be saying it does:
>
> http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html
> "
> FOR EACH ROW
> FOR EACH STATEMENT
> This specifies whether the trigger procedure should be fired once
> for every row affected by the trigger event, or just once per SQL
> statement. If neither is specified, FOR EACH STATEMENT is the
> default. Constraint triggers can only be specified FOR EACH ROW."
>
> Now it is entirely possible I am reading the above wrong and if
> that is the case I would welcome an explanation of where I am
> misinterpreting it.

Currently on an AFTER ... FOR EACH ROW we fire the trigger once
*for* each affected row, that's true.  But we don't do it
immediately after the *triggering event* -- we do it immediately
after the *data change statement*.  The issue isn't how many times
we execute the trigger, or with what parameters, but *when* it
runs.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company