We have created an update trigger for a join of two tables which is given below,
CREATE TRIGGER X
AFTER INSERT ON A
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE TESTVAR1 CHAR(1);
DECLARE TESTVAR2 CHAR(9);
SET (TESTVAR1,TESTVAR2) = (SELECT TEST_VAR1 TESTVAR1,
SELECT TEST_VAR2 TESTVAR2
FROM B
WHERE B.XXX = N.XXX
AND B.YYY = 'C')
INSERT INTO C
VALUES (N.XXX,N.YYY,
:TESTVAR1,:TESTVAR2,N.ZZZ) ;
END

When executing the above trigger,it is failing with sql code -919.Can anybody please help me out in solving the error?

Before creating trigger, you made some basic syntax errors in SELECT and INSERT statement.

For example:
1)
SELECT TEST_VAR1 TESTVAR1, SELECT TEST_VAR2 TESTVAR2 FROM B ...

2)
SET (...) = (...) INSERT ...

3)
VALUES (N.XXX,N.YYY,:TESTVAR1,:TESTVAR2,...


And, what are your platform, DB2 version/release, full error message?

Also, why the roundabout way to say
"insert into table
select n.xxx, n.yyy, b.TESTVAR1, b.TEST_VAR2
from table2
where..."

Dave