I am trying to update a field of the current inserted row in AS400. I am using version 4.5, 5.3 and 7.1.
For my tests, I have created a table WC (Work Center) containing a CRTDT field (NUMERIC 7,0)
When inserting a new record to this table, I want to check if the value of this field is less that 200,000 and if yes add to it 1,000,000.
My SQL is:
-- Generate SQL
-- Version: V5R4M0 060210
-- Generated on: 10/13/16 10:03:49
-- Relational Database: S65BEE7B
-- Standards Option: DB2 UDB iSeries
CREATE TRIGGER RAVONLIB.WC_TRIGGER
AFTER INSERT ON RAVONLIB.WC
FOR EACH STATEMENT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
IF WC.CRTDT<200000 THEN
UPDATE RAVONLIB . WC SET CRTDT = 1000000 + CRTDT ;
You should be using a
BEFORE INSERT trigger.
Additionally, you need to be modifying the column value as it is inserted...you can't run an SQL
UPDATE statement in a trigger on a row that was just inserted.
Lastly, you'd want a row trigger, not a statement trigger.
CREATE TRIGGER RAVONLIB.WC_TRIGGER BEFORE INSERT ON RAVONLIB.WC REFERENCING NEW AS new_row FOR EACH ROW MODE DB2ROW SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN ATOMIC IF new_row.CRTDT<200000 THEN SET new_row.CRTDT = 1000000 + new_row.CRTDT ; END IF; END ;