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