SQL Anywhere 22.214.171.1247
Does "Trigger Event" block the caller until the event has completed?
We're looking to call an event in an AFTER INSERT trigger which could potentially perform an expensive operation. So will the "Trigger Event" call block the insertion at all?
asked 15 Feb '11, 01:29
Events run on their own connection (and as a consequence, use separate transactions), so generally a TRIGGER EVENT does just that: It triggers the event and returns immediately.
Therefore, in general, both connections don't will block each other.
However, blocking might occur if the event's action would lead to locks on tables/rows that are accessed by the triggering connection, too. E.g. when the event would insert in the same table or update the same row as the trigger is declared for, then the usual "writers will block writers" problem might arise. But that will be dependent on exact timings (for example, whether the event's code starts before the AFTER TRIGGER is finished).
I guess your planned event is bound to read the row's contents after the insert statement has finished - then you have to make sure the isolation level does allow for that. If the transaction that has inserted is still not committed, the event (running in its own connection) might try to read a non-existing row when using a higher isolation level - or might read an uncommitted and later "rollbacked" change when using isolation level 0.
answered 15 Feb '11, 08:42
Be aware that you can run into timing Problems if you insert the Rows Faster as your Event can finish his task.
answered 16 Feb '11, 11:45