Re: triggers within commit cycles

["David Morris" <dmorris@xxxxxxxxxxxxx>]

Pete,

To get around the recursion problem and possibly the scoping problem,
you can use a CL or C stub to call your RPG procedure. Unfortunately,
SQL will always call the trigger from the default activation group, which
is usually a bad thing if you specify *CALLER on the trigger.  Also, to update
the file triggering the change in a trigger, you cannot share the access path.

David Morris

>>> pbhall@execpc.com 08/20/01 07:38PM >>>
At 10:01 08/20/2001, Charly Jones wrote:

>If I update a record in a file that has an update trigger, then rollback the
>transaction using commitment control, does the trigger program fire again
>with the correct update that puts the record back like it was?

The recommended procedure is to make the trigger execute within the same
commitment control definition as the program that caused the trigger to
fire. This can be accomplished in RPG by setting the Commit switch on the
ancillary file's F-Spec based on the trigger commitment level value in the
trigger buffer. With SQL, it can be done by compiling the program with
COMMIT(*NONE), which actually makes SQL conform to the commitment
characteristics of the parent process, which, of course, must be
responsible for committing or rolling back the transaction. The trigger is
not allowed to do that. When a transaction is rolled back, any changes made
by the trigger are also rolled back. The trigger doesn't need to fire
again, because the update never happened. Same goes for deletes and inserts.

Be very careful if the trigger program updates other rows in the table to
which it is attached. It must not update the row that triggered it (that
can be accomplished by updating the trigger buffer if the ALWREPCHG switch
is on when the trigger is added (ADDPFTRG)). It's OK to update other rows,
but that will also cause the trigger to fire, so (at least if you're using
RPG), it will be necessary to run the trigger in ACTGRP(*NEW) in order to
allow the trigger to be called recursively. If you need to do that, in
addition to making sure that you don't have any circular update
relationships, the commitment control definition must be scoped to the job
(it defaults to activation group).

Pete

Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/