RE: Simple Trigger Program

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Bob,

You're on the right track, a trigger program is a good way to do this.  In fact 
it's the only way to get what you want in near real time.

The other option would be to have a background program that processes journal 
entries.


However, the trigger idea may not be as "simple" as it seems.

The catch is that since trigger programs could be called for every row in a 
table and control is not returned to the program that caused the trigger to be 
fired until the trigger program is finished, trigger programs could have 
significant performance implications.  

In particular, if you're thinking about doing a dynamic CALL to an external 
program you could be asking for trouble.

In addition, is the file with the trigger processed under commitment control?

Lastly, for an SQL trigger that calls an external RPG program, you need to look 
at the following SQL statements:

CREATE TRIGGER 

CREATE PROCEDURE (External).


If you can have your external code as a procedure in a service program, instead 
of CREATE PROCEDURE, you'll want CREATE FUNCTION (External Scalar).

You're going to need:
iSeries DB2 UDB for iSeries SQL Reference manual 
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/info/db2/rbafzmst.pdf
 

also Redbook this will help:
Stored Procedures, Triggers and User Defined Functions on DB2 Universal 
Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

Have Fun!  

Post back with any questions.

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx
> [mailto:midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx]On 
> Behalf Of
> Bob O.
> Sent: Wednesday, June 22, 2005 1:30 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Simple Trigger Program
> 
> 
>    Here is what I am looking for...
> 
>    I need to detect any time a particular field's value 
> changes.  The field
>    is a 1 character field that is either blank, or contains 
> an 'X'.  Rather
>    than add some processing to the 20 or 30 programs that can 
> update this
>    field, I thought a trigger might be the answer.  If the 
> field changes to
>    an 'X' from *blank or *blank to an 'X', I want to call a 
> program to add
>    the changed record to another file to do some other processing.
> 
>    Unfortunately, I know squat about SQL.  So, if anybody has 
> an example of
>    the SQL I will need to do what I need, I would be 
> eternally greatful!
> 
>    >From: "Paul Tuohy" <tuohyp@xxxxxxxxxxxxx>
>    >Reply-To: Midrange Systems Technical Discussion 
> <midrange-l@xxxxxxxxxxxx>
>    >To: "Midrange Systems Technical Discussion" 
> <midrange-l@xxxxxxxxxxxx>
>    >Subject: Re: Simple Trigger Program
>    >Date: Wed, 22 Jun 2005 09:45:27 +0100
>    >
>    >Hi Bob,
>    >
>    >As of V5R3 (I think) you can define an SQL trigger at the 
> column level.
>    >
>    >If you use iSeries Navigator, expand the Databases option 
> to the required
>    >Schema and, from the context menu of the Triggers option 
> select New->SQL.
>    >You will see that you have an option for "Update of 
> selected columns".
>    >
>    >The downside (?) is that the trigger has to be an SQL script.
>    >
>    >External triggers (RPG, CL etc.) cal only be specified 
> for the record
>    level.
>    >
>    >HTH
>    >
>    >Paul Tuohy
>    >
>    >----- Original Message -----
>    >From: "Bob O." <otis_the_cat@xxxxxxxxxxx>
>    >To: <midrange-l@xxxxxxxxxxxx>
>    >Sent: Tuesday, June 21, 2005 3:30 PM
>    >Subject: Simple Trigger Program
>    >
>    >
>    > >    Is it possible to define a physical file trigger to 
> fire when a
>    >specific
>    > >    field is changed, or is it only possible to detect 
> the changed
>    record.
>    >We
>    > >    don't have any triggers on our files (it was like 
> that when I got
>    >here!),
>    > >    and I finally came across a reason to use one 
> (hence my very first
>    >trigger
>    > >    program).
>    > > --
>    >
>    >
>    >--
>    >This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing
>    list
>    >To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>    >To subscribe, unsubscribe, or change list options,
>    >visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>    >or email: MIDRANGE-L-request@xxxxxxxxxxxx
>    >Before posting, please take a moment to review the archives
>    >at http://archive.midrange.com/midrange-l.
>    >
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>