On 06/01/2013 06:47 AM, Kevin Grittner wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:
On 05/31/2013 08:15 AM, Kevin Grittner wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:
On 05/31/2013 06:32 AM, Kevin Grittner wrote:
But why? The OP specified FOR EACH ROW in the trigger
statement.
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.
Aah, that was the part I was missing. So to see if I understand, in the
OPs case:
1) The first case worked as Juliano expected because the INSERTs where
done in a loop where each INSERT was a discrete statement and there was
a 1:1 correspondence between statement and triggering event.
2) The second case did not work as expected because the INSERTs where
wrapped up in a single statement and the AFTER triggers ran for each row
after all the rows where inserted not after each row was inserted.
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
