Questions about triggers...

When you have cascading triggers, are they synchronis or asynchonis in nature. Scenario: You have end user (Fred) inserts a row in to table foo an after insert trigger tfoo is called. tfoo then inserts a row in to a second table called bar_queue. An after insert trigger called mailman then calls an external function. (THis is a simple messaging structure based on the inserted row.) So here are my questions. With an after insert trigger, does the row that gets inserted return or wait for the completion of the trigger? Is there a property that could be set to determine if the trigger should wait or no wait its operation? (Yes I know RFTM but I thought this might be a chance to give Daniel and Mark a break. ;-) _________________________________________________________________ Get FREE Web site and company branded e-mail from Microsoft Office Live http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/

Ian Michael Gumby wrote: > When you have cascading triggers, are they synchronis or asynchonis in > nature. I assume you're asking about synchronous and asynchronous? even after reading your scenario, I'm not completely clear what you're asking, but I think the basic answer is "synchronous". > Scenario: > > You have end user (Fred) > inserts a row in to table foo > an after insert trigger tfoo is called. > tfoo then inserts a row in to a second table called bar_queue. > > An after insert trigger called mailman then calls an external function. Is mailman a trigger on foo or bar_queue... > (This is a simple messaging structure based on the inserted row.) > > So here are my questions. > > With an after insert trigger, does the row that gets inserted return or > wait for the completion of the trigger? I simply can't work out what 'return or wait' might mean in this context. What isolation level are you working at? Are you concerned about the session that inserts the row into foo, or are you worried about other sessions? Are you in an explicit transaction, an implicit transaction, or using an unlogged database and hence there is no transaction at all? However, the basic behaviour is going to be that the set of rows (possibly a set with one element) are inserted into foo, and then the AFTER INSERT trigger is fired. At this point, the records are all 'in' table foo - at least as far as this transaction is concerned. The statement won't complete until the triggered actions complete - because any of the triggered actions might trigger an error that causes the statement to fail (so the statement cannot complete until the triggered actions have also completed). If the database is logged, then a failure in the triggered actions will cause the statement as a whole to fail, and the transaction's effects within the database will be undone. You can't unexecute external functions such as a function to send email to privileged (or is that 'abused') users. > Is there a property that could be set to determine if the trigger > should wait or no wait its operation? No; there are no controls over the behaviour of the trigger - beyond the obvious declarative ones (found in the manual). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/