Re: TRIGGERS BEHAVIOUR

Francoise Fabret (fabret@lola.inria.fr) wrote: : QUESTION 1: : When FOR EACH ROW informix triggers are executed ? : i)After the SQL Statement ? : ii) Immediately after insert, delete, or update operations : to individual tuples only? ii. Immediately after the trigger event for each row (tuple). : QUESTION 2 : : Is the action part of a informix trigger interruptable? : That is, if a trigger TR0 executes two SQL statements S1 then S2 : in its action part and if S1 triggers another trigger TR1, what : is the resulting execution : : SCENARIO 1 : The action of TR0 is interrupted; TR1 is executed; then, : S2 executes after TR1 terminates. : SCENARIO 2 : The execution of TR1 is delayed until TR0 is terminated. Scenario 1. The order of execution will be TR0, S1, TR1, S2. : QUESTION 3 : : If the execution follows SCENARIO 2 : : : What happens in this new SCENARIO 3 : : Given a new Trigger TR2 defined on a different triggering events : from TR1, assume that S2 also triggers TR2 and TR3. The only time this will be true is if TR1 and TR2 are both update triggers and their triggering events are updates to different columns. You cannot have multiple insert or delete triggers for the same table; if you have more than one condition on which you want a triggered action to take place, you need to specify those conditions with a WHEN clause in the trigger. : i) Once TR0 is terminated, what trigger is first : executed TR1? TR2? TR3? : ii) Suppose that TR1 is chosen and that in turn TR1 triggers TR2. : ---> What trigger is then executed? TR2? TR3? : --->Is TR2 executed once or Twice ? : (one time for the triggering event : within TR0 and one another time for the triggering event within : TR1's action? (in particular, is it possible to have the following execution : order : TR2 then TR3 then TR2. If there are more than one update trigger for a table, each specifying different columns as their trigger events, the order in which the triggers are executed is a function of the order in which the columns are defined in the table. For example, if you: CREATE TABLE tab1 (col1 INT, col2 INT);and you: CREATE TRIGGER TR1 UPDATE ON tab1(col1)....; and: CREATE TRIGGER TR2 UPDATE ON tab1(col2)....; and then: UPDATE tab1 SET (col1, col2) = (1, 2); TR1 will be executed first because it's trigger event is the first- specified column in the table schema. Now, if TR1's trigger action cascades into another trigger, it will be executed before TR2. In no case can you create a circular loop. (I know; I've tried.) such that, for example, TR1 will result in the update of tab1(col2) and then TR2 will result in the update of tab1(col1). A trigger action will not be allowed to modify the object of a trigger event. : Thank you for your help ! You're welcome. : Francoise & Francois -- ___ ___ Principal Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own. Eventually all things merge into one...and a river runs through it. -N.Maclean