RE: Simple Trigger Program

["Bob O." <otis_the_cat@xxxxxxxxxxx>]

  Ugghhh...  I think I'll just add the 10 or 12 lines of code to the
  programs.  I need to do this sooner than the time it would take me to
  learn all this SQL stuff...

  >From: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
  >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 13:56:31 -0400
  >
  >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.
  > >
  > >
  >
  >--
  >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.
  >