DB2 trigger issues

DB2 Mainframe version 12.

We have a table that different applications insert into. The applications all have field1 value but might not have field2. Field2 can be retrieved from another existing table using field1. So to update field2 from the existing table we tried the following triggers:

CREATE TRIGGER GENTRIGG                                 
NO CASCADE BEFORE INSERT ON Table1
REFERENCING NEW AS NNN                                       
FOR EACH ROW                                                 
BEGIN                                                        
SELECT Table2.field2  INTO NNN.field2 FROM
Table2
WHERE Table2.field1  = NNN.field1
FETCH First 1 rows only;                           
END

Also tried

CREATE TRIGGER GENTRIGG                                      
NO CASCADE BEFORE INSERT ON Table1
REFERENCING NEW AS NNN                                        
FOR EACH ROW                                                 
BEGIN                                                        
SELECT Table2.field2  INTO NNN.field2 FROM
Table2
WHERE Table2.field1  = NNN.field1 AND
Table2.field3  = ‘0’;
END

where field3 = 0 will return only one row.

Tried MODE DB2SQL with ATOMIC as well. But all of them failed with :

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: AT YEAR YEARS MONTH MONTHS DAY DAYS HOUR HOURS MINU

Or something similar.

TIA

Expected the TRigger creation to work but it does not. Keep getting -104

Solution

Do you mean something like this:

CREATE TRIGGER GENTRIGG    
NO CASCADE BEFORE INSERT ON Table1
REFERENCING NEW AS NNN    
FOR EACH ROW    
BEGIN    
  if NNN.field2 IS NULL
  then
    set NNN.field2 = (Select field2 
                      from Table2 
                      where field2 = NNN.field1 
                      fetch first 1 rows only);
  end if; 
END

You should consider making it more defensive, in case the subquery also returns null.

To execute the above, the tool you use to submit the SQL to the database needs to be configured correctly to use an alternative delimiter at the end of the block (after the END of the trigger). The way to configure it varies with the tool.