Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    12

    Unanswered: Continue handler not working in trigger

    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'!]
    Last edited by sordax; 02-27-08 at 04:45.

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    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
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Feb 2005
    Posts
    12
    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...

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What does "the trigger fails" mean?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2005
    Posts
    12
    Quote Originally Posted by stolze
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •