How to use a sequence with insert all statement, produces ORA-00001 error
426850Nov 9 2006 — edited Nov 9 2006Here is a test-case for what we try to avoid;
CREATE SEQUENCE seq_customer ;
CREATE TABLE CUSTOMER(cust_no INT PRIMARY KEY) ;
INSERT ALL
WHEN(1 = 1) THEN
INTO CUSTOMER VALUES (seq_customer.NEXTVAL)
INTO CUSTOMER VALUES (seq_customer.NEXTVAL)
SELECT * FROM dual ;
Returns: ORA-00001: unique constraint (HR.SYS_C007431) violated
CREATE OR REPLACE TRIGGER trg_bi_customer
BEFORE INSERT ON customer
FOR EACH ROW
BEGIN
IF :NEW.cust_no IS NULL THEN
SELECT seq_customer.NEXTVAL INTO :NEW.cust_no FROM dual;
END IF;
END;
/
INSERT ALL
WHEN(1 = 1) THEN
INTO CUSTOMER VALUES (seq_customer.NEXTVAL)
INTO CUSTOMER VALUES (seq_customer.NEXTVAL)
SELECT * FROM dual ;
Also Returns: ORA-00001: unique constraint (HR.SYS_C007431) violated
Any comments will be welcomed,
Thank you.
Kind regards.
Tonguç