Re: Why are triggers semi-deferred?

On Mon, 5 May 2003, Philip Warner wrote:

> At least in 7.3, triggered actions specified as AFTER seem to be deferred
> to the completion of the outer-most SQL statement. So, if two triggering
> statements are executed as part of a PLPGSQL procedure, they will not be
> executed until the outermost statement finishes.
>
> As far as I can tell, this is not the way the spec says it should work:
>
> The <triggered SQL statement> of a triggered action is
> effectively executed either immediately before or immediately
> after the trigger event, as determined by the specified
> trigger action time.
>
> In the case of statements executed sequentially inside a PLPGSQL procedure,
> I would have expected that the trigger would fire after the first
> triggering statement.

Actually, I think from sql99's description, for after row triggers it
should happen after the row is modified not after the statement as a
whole (so given two 2 row updates in a function you'd get
update1,row1 afterrow1-1 update1,row2 afterrow1-2,afterstatement1
update2,row1 afterrow2-1 update2,row2 afterrow2-2,afterstatement2
)

I'd been thinking (this came up recently is a bug/query about the foreign
keys that Tom sent me) that it might be better to make times that the
triggers can run be choosable (with the spec behavior becoming default
eventually) because we've got backward compatibility issues and we've kind
overloaded the trigger system to do the foreign keys which have their own
timing issues.