Trigger error: duplicate rows for those columns

grofaty · #1 (**permalink**) 04-06-09, 05:20

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

stolze · #2 (**permalink**) 04-06-09, 06:57

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.

tonkuma · #3 (**permalink**) 04-06-09, 09:06

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

Internet Services

Web Development

Digital Marketing

Consumer Tech