Hi All,

I hope that they can help me.

I need to use a SELECT INTO statement, because i want get a value from table in a trigger.

This is my code:

----------------------------------------------------
CREATE TRIGGER DB2ADMIN.FAC_PROGR_INS
AFTER INSERT ON DB2ADMIN.XTFACOM1F
REFERENCING NEW AS INSERTADO
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE v_NombProvee CHAR(80);
DECLARE v_NombPrPg CHAR(80);

SELECT nombcomp INTO v_NombProvee
FROM DB2ADMIN.MPERSO1F
WHERE compania = INSERTADO.compania
AND persona = INSERTADO.proveedor;

SELECT nombcomp INTO v_NombPrPg
FROM DB2ADMIN.MPERSO1F
WHERE compania = INSERTADO.compania
AND persona = INSERTADO.provpago;
END

INSERT INTO TEMPTABLE(C1,C2)
VALUES(v_NombProvee,v_NombPrPg );

END
----------------------------------------------------


...and I get the message error:

--------------------------------------------------------------------------
[IBM][CLI Driver][DB2/NT] SQL0104N Se ha encontrado un símbolo imprevisto "v_NombProvee"
a continuación de "SELECT nombcomp INTO". Entre los símbolos esperados se puede incluir: "<space>".
LINE NUMBER=1. SQLSTATE=42601
-------------------------------------------------------------------------


Whats is the problem?

Thanks Advisors.

Wil.

Please, ignore the first END, forgets to clear it when reducing I code. The message is the same.

Thanks,

Wil

SELECT .. INTO is not a dynamic SQL statement and therefore cannot be used in a trigger. You could try using instead. Better yet, use

I'm not sure, but I think "SELECT INTO" cannot be used in triggers... (?)

How about if you write the select's as following ?

SET v_NombProvee = (SELECT nombcomp
FROM DB2ADMIN.MPERSO1F
WHERE compania = INSERTADO.compania
AND persona = INSERTADO.proveedor);

SET v_NombPrPg = (SELECT nombcomp
FROM DB2ADMIN.MPERSO1F
WHERE compania = INSERTADO.compania
AND persona = INSERTADO.provpago);


Antti

Must be that the triggers not support the SELECT INTO in this version.

Well, now i would to use the other variant.

Thanks for your help.

Wil