Hi,

Can any body provide some assistance on creating an after trigger in DB2 UDB V8.1 with a call to a Stored Procedure.
The Stored Procedure modifies SQL data and also the transition variable "NEW" is to be updated in the trigger.

The trigger code is givenj below:-

CREATE TRIGGER trgLocChngTranSrl1 AFTER INSERT ON TABMPLOCATIONCHANGE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

DECLARE vcNewRowId VARCHAR(40);



DECLARE nCount FLOAT;



CALL PRCRECORDIDGENERATION('-1','MN','LocationChange',CAST (NULL AS VARCHAR(1)),
CAST (NULL AS VARCHAR(1)),CAST (NULL AS VARCHAR(1)),CAST (NULL AS VARCHAR(1)),CAST (NULL AS VARCHAR(1)),vcNewRowId);



SET NEW.INTTRANSACTIONSERIALNUMBER = ORA.ROUND(ORA.TO_NUMBER(vcNewRowId));

END!

The error message is:-

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0797N The trigger "DB2ADMIN.TRGLOCCHNGTRANSRL1" is defined with an
unsupported triggered SQL statement. LINE NUMBER=21. SQLSTATE=42987

Kindly provide the necessary alternatives to create this type of a trigger

Regards,
Mahesh.B.S

You cannot use the SET - command in an AFTER trigger as the insert is already done

you must use the UPDATE table statement if you need to change the values just inserted.