At 10:25 PM 10/06/2011, André Knappstein, Controlling wrote:

>OK> I think, despite that the stored procedure inserts all data in one
>OK> transaction, the trigger runs before the other records were inserted.
>
>I am probably not the right person to dare such a statement, but isn't that as it should be, at least if your transaction is reading only committed versions?
>
>Your trigger runs when the records have been inserted, but the insert is not yet committed.
>
>It is possible to configure transactions to read uncommitted inserts, but that would be by far too hot to handle for me :)

*The transaction that his procedure runs in* (call it ThisTransaction) indeed DOES read the uncommitted inserts that its statements have executed. A transaction sees all of its own work. Have you been handling this hot thing all this time, without knowing? ;-)

It seems that he is perhaps confused, thinking that the steps through a procedure and the involved trigger actions are in some way queued and deferred until the commit. That is not what happens. Instead, an action is executed in its actual sequence and execution proceeds onward inexorably.

When everything is done, control returns to the client that owns the transaction.
-- If the client commits the transaction, exactly the same changes that the (now defunct) transaction could see before are now visible to other transactions.
-- If the client rolls back the transaction, then NO changes, whether effected by the procedure or by triggers, will be visible to any transaction.

./hb