Hi! I can't get a continue handler to work in an after insert trigger. I want it to handle a duplicate key error originated in a second insert inside trigger. But instead of the handler defined action being executed the triggers fails.

This is what I do:

CREATE TRIGGER MY_TRIGGER
AFTER INSERT ON TABLE1
REFERENCING NEW AS NROW
FOR EACH ROW
MODE DB2ROW
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE V_STATE DECIMAL(3);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET V_STATE = 3;

SET V_ESTADO = 1;

INSERT INTO TABLE2 (...) VALUES (...);

UPDATE TABLE1 SET STATE = V_STATE, SQLERR = SQLCODE WHERE ID = NROW.ID;

END;

I've also tried SQLSTATE '23505' instead of SQLEXCEPTION with same results...

Am I doing something wrong?

I'm on IBM iSeries 5.3

Thanks in advance!

[Edit: not SQLSTATE '12345' but SQLSTATE '23505'!]

this seems some code for a stored procedure, not for a trigger
have a look at this diagram
http://publib.boulder.ibm.com/infoce...c/r0000931.htm

Sorry, but I don't quite understand. It has the right structure for a trigger, what's wrong for you?

I've also tried moving all the code into a stored procedure and calling it from the trigger with same results...

What does "the trigger fails" mean?

It means that the insert operation performed in the trigger is failing cause of a duplicate key error (sql: -803, sqlstate: 23505), instead of it being handled by the continue handler. This interrupts trigger execution and rollbacks the triggering operation.