Re: multiple trigger programs for many data files - is there a bettermethodology?

Booth -

No offense intended, but Pete's example program on your web page is NOT a trigger mediator program - it is a trigger-handling program.

Big difference...

Perhaps you should read more about Alan's trigger mediator program:

http://think400.dk/files/trigger%20mediator%20documentation%20v2r1.doc

Excerpt:
Trigger Mediator Documentation


What is a Trigger Mediator?


A Trigger Mediator is a program that mediates trigger requests between the iSeries Database (DB2) and a trigger service programs. By mediate, we mean, DB2 calls the trigger mediator and the mediator, in turn, calls the correct trigger service program. It “Mediates” all requests.



By using a mediator, we have the ability to, remove all tables from triggers, activate triggers on all tables, add new table to triggers, remove a table from triggers, all instantly.



Triggers are powerful tools to capture changes made to the DB2 database but triggers have several problems that make them difficult to use.



1.. Triggers put an exclusive lock on the trigger program. It cannot be removed except by removing the trigger from the table. If errors are detected in the trigger program, they cannot be corrected without removing the table from triggers.
2.. Triggers put an exclusive lock on tables when they are under trigger control. They cannot be deleted or changed while under trigger control.
3.. When enabling a trigger, you must put the name of the individual program to call for each event, Add, Update, Insert.
4.. The only way to disable triggers is to remove triggers from all tables.
5.. Triggers cannot be disabled except by removing the trigger from the table and then adding the trigger back.
6.. Triggers can represent security risks.


The Trigger Mediator solves all these problems.



Notes on Operation.


This version of the mediator supports recursion. By that we mean a trigger service program can issue an Add, Update or Delete that invokes the trigger mediator again. A few lines of C that form the entrance procedure support this.



How does the Trigger Mediator work?


The Trigger Mediator consists of a single program TG_MED that is called for all trigger requests. For example,



ADDPFTRG FILE(LIBX/FILEX) TRGTIME(*BEFORE) TRGEVENT(*INSERT) PGM(LIBX/TG_MED)



ADDPFTRG FILE(LIBX/FILEX) TRGTIME(*BEFORE) TRGEVENT(*DELETE) PGM(LIBX/TG_MED)



ADDPFTRG FILE(LIBX/FILEX) TRGTIME(*BEFORE) TRGEVENT(*UPDATE) PGM(LIBX/TG_MED)



In this scenario, each time before a record is inserted, deleted or update, DB2 will call the trigger mediator. It is up to the programmer to determine whether to call the trigger before or after or what events to monitor just as long as all trigger requests go through the trigger mediator.



It is up to programmer or administrator to do the ADDPFTRG command and add the triggers to the database. I have been thinking about add a command to the ADD/EDIT/DELETE screens to ask if the user would like to add the triggers to the physical file but have not had time to add it. IBM recommends that the trigger program be in the same library as the physical file. If this is not possible, make sure if you restore databases, that the Trigger Mediator program (TG_MED) gets loaded first. The trigger service programs can be loaded at anytime as long as everything is there before the first time the mediator is called.



[...]




- sjl


Booth wrote:
Here is a copy & paste that would be a good mediator. This comes from
Pete Hall, and is a few years old. It may do exactly what you'd like,
but you would need one for each file.

http://www.martinvt.com/Code_Samples/Trigger/trigger.html

What is significant though is that the trigger program does not have an
F-spec for the trigger file. The record is passed as a before image and
an after image. That gives you choices, but the significant point is to
make the trigger program simple. Don't do any processing in it beyond
the trigger itself. Put all of the complexity in non-trigger programs
that are easily changeable.


Joel wrote:

I have been tasked with copying records from several files to an

external system when data in records change.

So a series of trigger programs seems like a good solution.
I will have a pile of20 files, but lets say there are 3 files for

simplification.

I need a different trigger pgm for each file:

File Program
------- -------------------
Cust pgmA

Product pgmB

Order pgmC


Would it be possible and worthwhile to build only one trigger pgm? I

was thinking that maybe there is a way to capture ONLY the file-name and
the primary key values into a new file.

Then later when the data transfer to the external system occurs, an SQL

command would take the file name& keys and get the actual full data record
to transfer.

Would this be worth the extra work to make one flexible pgm to handle

all data files?

I could possibly make use of the system files QADBKFLD and QADBIFLD to

construct keys and files to identify changed records.

Can this be done?? Or not worth it??

Has anyone done this? Can you share some code??