Re: RPGLE Trigger programs and Commit Control

Well,

Thanks for your comments.

I understand that things don't work the way I would like, and probably for
good reasons.

However from my (possibly simplistic) point of view:

1) If the database manager held back on the trigger processing until the
commit. Life would be simple. There would be nothing to undo. I'm using
commit control, that's a declaration that I don't want to declare the
transaction over until I commit. Why let processing bleed over into the
trigger's domain until I've confirmed the transaction with a commit?
2) It makes no sense to me that a ROLLBK would not fire the trigger. The
UPDATE changes a record, the trigger gets called and given the latest state
of the record. Then a ROLLBK changes the record back. Why wouldn't the
trigger program get called again to reflect the changed record state? The
database was changed by the ROLLBK just as it was changed by the UPDATE. It
seems to me that the trigger program should get called for both events or
neither. Not just for one of them.

Thanks for the info on the API. I'll have a look at it although I don't
expect it will help in my particular case.

I'm not really a fan of triggers. Partly because I grew up in a world where
applications controlled everything and partly because much of my experience
of people using triggers is not because they see it as an elegant design
but because they don't want to have to revisit swathes of complicated
application programs and try to build the transactions into them. The
latter being the reason why I'm being asked to provide a trigger solution
in this case.

I suppose the route I will choose will be to allow the trigger program to
process the "simple" transactions where commit control is not required and
tackle the more complicated cases by making changes in the application
layer. I've built a process into the trigger programs to allow updates from
certain callers to be ignored ( on the grounds that an update by a
particular process will be part of a bigger transaction and therefore
processed via different means )

Thanks as always to those who took time to pass on their thoughts.
best regards,
Craig

On 6 June 2018 at 21:13, Paul Roy <paul.roy@xxxxxxx> wrote:

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

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
digest 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 http://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

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

End of RPG400-L Digest, Vol 17, Issue 283
*****************************************

--
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