Update Trigger on Insert in AS400

Question

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 
    MODE DB2SQL 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN ATOMIC 
IF WC.CRTDT<200000 THEN
    UPDATE RAVONLIB . WC SET CRTDT = 1000000 + CRTDT ; 
END IF;
END  ;

I am getting an error that Variable CRTDT not defined or not usable. What is the correct syntax in order to update CRTDT properly in case it is smaller than 200000?

Answer 1

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  ;