Re: RPGLE Trigger programs and Commit Control

Hi,

I could be wrong but the behavior seems normal to me (thinking data base
and transaction...)
when the transaction is successful, all change to the data base
(including ones that are run by the trigger) are committed and written to
the journal...
when the transaction cannot complete.. all changes to the data
base(including the ones triggered) are rolled back from the 'before image'
in the journal...

this is a question of DB design.... all changes to the DB should
(normally) be part of the transaction...

However, I have faced one situation where the call of the trigger program
by the rollback event could have been very useful..
(the trigger calls a webservice to update another remore data base)...
Could be worth a RFE... I will vote for it.

Paul






message: 2
date: Wed, 6 Jun 2018 10:27:19 -0600
from: Nathan Andelin <nandelin@xxxxxxxxx>
subject: Re: RPGLE Trigger programs and Commit Control

I was aware that trigger programs may be called before and after DB events
(i.e. read, write, update, delete), and that commits don't invoke trigger
programs. I hadn't explored what happens during rollbacks. But it doesn't
surprise me to hear that they don't invoke triggers because programs may
perform thousands of writes, updates, or deletes before reaching a
commitment-control boundary (where either commit of rollback might be
invoked), By that time, trigger program logic may screw up data. So I
agree
that it's safer and makes more sense if commit and rollback don't invoke
trigger programs.

Of course, you'll have to decide whether your process really needs both
trigger programs and commitment control. It sounds like commitment control
and trigger programs may not be a good fit.


On Wed, Jun 6, 2018 at 9:57 AM, Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Hi All,

I'm just trying to consider the implications of having an RPGLE trigger
program on a file which is being updated by many programs, some of which
use commit control and some of which do not.

At the moment the trigger program runs in the caller's activation group

and

is not using commit control itself.

The first thing I noticed is that the trigger program gets called when

the

UPDATE is performed, not the COMMIT, I guess I can understand why this
might be in terms of database integrity but it makes life tricky because

if

the application decides to do ROLLBK, the trigger has already done it's
stuff and in my case is initiating asynchronous processing which could

only

be undone by building a set of reversal transactions.

Which brings me to my second point.

If the Application performs a ROLLBK rather than a COMMIT, I thought

that

the subsequent change to the database would fire the trigger program

again.

Allowing me the possibility to build a reversing transaction. My testing

so

far indicates that the trigger program only gets called after the

initial

UPDATE and not as a result of the ROLLBK. This does not make sense to

me.

I understand that I could build commit control into the trigger program

but

in that case the normal design would be to have it in the same commit

block

and controlled by the program doing the file update.
I don't see how this can work well because the trigger programs gets

called

by the UPDATE, at which point it goes ahead and does it's processing. If

a

rollback ensues, it's too late, the trigger program has already

initiated

the asynchronous processing. Yet the trigger program cannot hold back

it's

processing until the COMMIT because as far as I can see it only gets
invoked by the UPDATE.

Has anyone else wrestled with triggers and commitment control?

Does anyone disagree with my assertion that:
1) The trigger program gets called when the UPDATE is issued not when

the

COMMIT is issued.
2) The trigger program does not get called if the record is reverted to
it's original state via a ROLLBK.

thanks and regards,
Craig
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

------------------------------

Subject: Digest Footer