ehh ehh - 1 month ago 10
SQL Question

Update Trigger on Insert in AS400

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

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  ;
Comments