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
CodePudding user response:
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.