Re: Is there a way to add a Where clause to an SQL After Insert Trigger?

On 30-Apr-2015 14:30 -0500, Jeff Young wrote:

V6R1. <<SNIP>>
I have successfully created After Insert and After Update SQL
triggers.
I would like to be able to have a filter on the After Insert trigger
so that only records meeting my selection criteria will be processed.
Is this possible?
I have not been able to find anything in the SQL Reference Manual
regarding this.

Add the -WHEN--(--search-condition--)- logic [from the syntax diagram]
for the /triggered-action/ on the CREATE TRIGGER ... AFTER INSERT, having
qualified the column names with the /correlation-name/ specified on the
REFERENCING NEW ROW AS clause.

The following is a trivial example, tested on v5r3; depends on a
procedure I have called REXSAY and a prior table ITEM that was created with
two character data type columns named IK01 and IK02 irrespective them being
actual keys:

create trigger item_ai
after insert on item
referencing new row as new
for each row
when ( new.IK01='' AND new.IK02='' )
call rexsay ('Empty key')

My current version of that REXSAY is near-identical to what I have
written at <
https://groups.google.com/d/msg/comp.sys.ibm.as400.misc/3Ud9tveCeks/8dIYNoBsgR8J>;
in my current copy, the QSQXCUTE was overwritten with blanks [though I do
not recall why I made that change].

--
Regards, Chuck


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