Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Trigger error: duplicate rows for those columns

    Hi,
    I would like to insert one new record to table after each insert with default value 1000.

    I tried the following:
    1. clean up whole table (for test purpose)
    DELETE FROM ADMIN.TEST @

    2. drop trigger if exists
    DROP TRIGGER ADMIN.TEST_I@

    3. create trigger:
    CREATE TRIGGER ADMIN.TEST_I
    AFTER INSERT ON ADMIN.TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    INSERT INTO ADMIN.TEST VALUES (NEW.ID, 1000, NEW.ONOFF);
    END
    @

    4. testing insert statement
    INSERT INTO ADMIN.TEST VALUES (1, 1, '1')@

    Above command retuns error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0723N An error occurred in a triggered SQL statement in trigger
    "IZPIS.DOSTOP_I". Information returned for the error includes SQLCODE "-803",
    SQLSTATE "23505" and message tokens "1|IZPIS.DOSTOP". SQLSTATE=09000


    SQL0803N One or more values in the INSERT statement, UPDATE
    statement, or foreign key update caused by a DELETE
    statement are not valid because the primary key, unique
    constraint or unique index identified by
    "<index-id>" constrains table "<table-name>" from
    having duplicate rows for those columns.

    ============================
    Table columns definitions
    ============================
    ID INTEGER NOT NULL
    ZAPST INTEGER NOT NULL
    ONOFF CHARACTER (1) NOT NULL

    ===================
    Primary key columns
    ===================
    ID
    ZAPST

    ===================
    My system
    ===================
    Linux
    DB2 v9.5 fixpack 2

    Any idea what is wrong with trigger?
    Regards

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You insert a second row with the same ID. Assuming that ID is a primary key or a unique key, the duplicate value violates that unique constraint and you get the error.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The INSERT in your trigger fires again your trigger.

    Here is an easy walk around.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE ADMIN.TEST
    (ID    INTEGER      NOT NULL
    ,ZAPST INTEGER      NOT NULL
    ,ONOFF CHARACTER(1) NOT NULL
    ,PRIMARY KEY(id, zapst)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER ADMIN.TEST_I
    AFTER INSERT ON ADMIN.TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    IF new.zapst <> 1000 THEN
       INSERT INTO ADMIN.TEST VALUES (NEW.ID, 1000, NEW.ONOFF);
    END IF;
    END 
    @
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO ADMIN.TEST VALUES (1, 1, '1')@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM ADMIN.TEST@
    ------------------------------------------------------------------------------
    
    ID          ZAPST       ONOFF
    ----------- ----------- -----
              1           1 1    
              1        1000 1    
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    DROP TRIGGER ADMIN.TEST_I@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER ADMIN.TEST_I
    AFTER INSERT ON ADMIN.TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    /*
    IF new.zapst <> 1000 THEN
    */
       INSERT INTO ADMIN.TEST VALUES (NEW.ID, 1000, NEW.ONOFF);
    /*
    END IF;
    */
    END 
    @
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO ADMIN.TEST VALUES (2, 2, '2')@
    ------------------------------------------------------------------------------
    INSERT INTO ADMIN.TEST VALUES (2, 2, '2')
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0723N  An error occurred in a triggered SQL statement in trigger 
    "ADMIN.TEST_I".  Information returned for the error includes SQLCODE "-803", 
    SQLSTATE "23505" and message tokens "1|ADMIN.TEST".  SQLSTATE=09000
    Last edited by tonkuma; 04-06-09 at 10:45.

Posting Permissions

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