Re: SQL Trigger Information

On 26-Mar-2015 09:05 -0500, Jeff Young wrote:

On 25-Mar-2015 14:27 -0500, Jeff Young wrote:

On 24-Mar-2015 15:30 -0500, Jeff Young wrote:

I have a file that I would like to setup a trigger for when
selected fields are changed or a record is added and have it call
an RPGLE program.

Can I do this with SQL?

If so, does anyone have an example to share?

System is at V6R1M1.

<<SNIP>> While I could just create a normal trigger for Add or
Update and then code a program to test just those fields, I thought
that perhaps using an SQL Trigger for only those columns on each
file, that I could just extract the data I need without having to
code any field change testing.

<<SNIP>> As I indicated, I could just set a normal trigger and have a
program test each of the before and after fields, but it would be
neat if I could have the system do that for me using SQL Trigger on
the columns.

Coding the testing of the fields, of course, can not be avoided. However that testing can be coded [perhaps arguably more conveniently] as SQL, and the biggest benefit is that the invocation of the trigger will be prevented except when the coded conditions are met; the external [non-SQL] trigger would need to get invoked and the trigger program would then decide if\what was to be done based on the change-conditions detected.

I can not test, but I expect the following script [as snippet from an actual example] should run without error when using System Naming option [even if the CREATE PROCEDURE has not been run]; although simplistic, hopefully sufficient detail remains in the example from which to glean how to achieve what was asked for both an INSERT and an UPDATE [that is conditioned for on which fields and even on field values]:

create table doc_file
( document_id for docId char ( 7) not null
, document_lvl for docLvl decimal (5, 2)
, document_txt for docTxt varchar ( 155) not null
, document_dte for docDte date not null
)
;
create procedure myRPGLE
( in char(07)
) language RPGLE
parameter style general
external name myRPGLE
;
/* triggers refer to the column long-names */
create trigger doc_file_ai
after insert
on doc_file
referencing new as n
for each row mode db2row
begin
/* prior CREATE PROCEDURE defined myRPGLE pgm to SQL */
call myRPGLE ( n.document_id ) ;
end
;
create trigger doc_file_au_dl
after update
of document_lvl /* trg only if docLvl is changing */
on doc_file
referencing new as n old as o
for each row
when ( current_user <> 'DOCADMIN'
and n.document_lvl IS NOT NULL
) /* no action on Upd by DocAdmin or a docLvl reset */
begin
/* if ( (n.document_lvl - o.document_lvl) > 1 ) */
/* then signal... */
/* prior CREATE PROCEDURE defined myRPGLE pgm to SQL */
call myRPGLE ( n.document_id ) ;
end