Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval
icon9.gif  PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580408] Sun, 24 March 2013 04:27 Go to next message
chankx91
Messages: 5
Registered: March 2013
Junior Member

I need a really big help from u all ,Im a newbie , this is the first time I'm writing trigger.

I want insert/update records to another table(MICL_SUPERVISORS) using Trigger (pl/sql oracle 10g).

When trigger fired its giving an error as ORA-00001: unique constraint violated. I know it happen because I want to add SUPID from sequence ( Select micl_sup_id_seq.nextval into nSUPID from dual;). And this is happening inside a loop.

SUPID- column is primary key in my table( MICL_SUPERVISOR). So I cant drop that constraint.

Once I tried auto incrementing but it take long time and it didn't work well and it is slow. I have thousands of records in this table. I did it as SELECT MAX((SUP_ID)+1 from micl_sup_id_seq

Due to this Error I did a small research and found out we cannot use seq.nextval inside a trigger. So my question is is there any easy, accurate way to achieve this.??? pls help me Sad Im struggling this for weeks and weeks

thanks in advance

Here is the code (it all happening inside if clause else part is working Fine. Pls note that I have use a cursor , inside open cursor all this happen)

create or replace
TRIGGER "c"."INSERT_MICL_SUP_DETAILS" AFTER INSERT OR UPDATE OF "ID","SUP_EMP_NO","EMP_NO" ON "MIMAX"."EMP" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW

DECLARE miclaim_supervisor_count number; employee_company_code VARCHAR2(10); employee_businessunit number; projMgr NUMBER; nSUPID NUMBER;

cursor projMgrsCursor is select b.BU_MEMBER_ID
from BU_MEMBER b, EMP_SUB_DIV s
where s.EMP_NO = :NEW.EMP_NO
and s.SUB_DIVISION_CODE = '0345' and s.DIV_CODE = '1010'
and b.BU_ID IN (select BU_ID from BU_MEMBER where BU_MEMBER_ID = :NEW.EMP_NO);

BEGIN
delete from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 0;
select count(*) into miclaim_supervisor_count from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 1;
select COMPANY_CODE into employee_company_code from EMPLOYEE_MASTER where EMP_NO = :NEW.EMP_NO;
projMgr := 0;


if (employee_company_code ='SOFT')then

OPEN projMgrsCursor;
LOOP

FETCH projMgrsCursor INTO projMgr;
EXIT WHEN projMgrsCursor%NOTFOUND;

select micl_sup_id_seq.nextval into nSUPID from dual;

insert into MICL_SUPERVISORS (SUP_ID,ASSIGNED_DATE, ASSIGNED_BY_EMP_NO,AMOUNT_LIMIT,IS_OVVERRIDDEN , SUP_EMP_NO,RTD_EMP, EMP_NO)
VALUES ( nSUPID, (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, projMgr, NULL, :NEW.EMP_NO);

END LOOP;
CLOSE projMgrsCursor;
else
if(miclaim_supervisor_count IS NULL or miclaim_supervisor_count<1) then
insert into MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, :NEW.SUP_EMP_NO, NULL,:NEW.EMP_NO);
end if;
end if;

END; Sad Sad

If anything unclear ask me I'll explain furthermore about this scenario , I hope anyone will help to solve this problem thanks
Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580409 is a reply to message #580408] Sun, 24 March 2013 05:07 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
chankx91 wrote on Sun, 24 March 2013 10:27

. . .
Due to this Error I did a small research and found out we cannot use seq.nextval inside a trigger. So my question is is there any easy, accurate way to achieve this.???
. . .


I don't remember to have read in oracle documentation that seq.nextval cannot be used inside a trigger (actually that's one of the advantages to generate primary keys). Do you have the link of that document?

Anyway, here is an example indicating that you can use sequence within a trigger.

DROP SEQUENCE myseq;
CREATE SEQUENCE myseq;


DROP TABLE students;
CREATE TABLE students
(
    student_id      NUMBER          NOT NULL,
    firstName       VARCHAR2(30)    NOT NULL,
    lastName        VARCHAR2(30)    NOT NULL
);
ALTER TABLE students ADD CONSTRAINT STUDENTS_PK
    PRIMARY KEY(student_id);

    
    
CREATE OR REPLACE TRIGGER students
BEFORE INSERT
ON students
FOR EACH ROW
WHEN (NEW.student_id IS NULL)
BEGIN
    SELECT myseq.nextVAl
    INTO :NEW.student_id
    FROM DUAL;
END;
/
SHOW ERRORS;
   
   
INSERT ALL
    INTO students(firstName, lastName) VALUES ('fname1', 'lname1')
    INTO students(firstName, lastName) VALUES ('fname2', 'lname2')
    INTO students(firstName, lastName) VALUES ('fname3', 'lname3')
    INTO students(firstName, lastName) VALUES ('fname4', 'lname4')
SELECT * FROM DUAL;



And we check to see whether the keys were generated

SQL> SELECT * FROM students;
STUDENT_ID FIRSTNAME			  LASTNAME
---------- ------------------------------ ------------------------------
	 1 fname1			  lname1
	 2 fname2			  lname2
	 3 fname3			  lname3
	 4 fname4			  lname4

SQL> 




Apart from this example, your description of your problem is not clear/detailed, so I cannot tell you why you get errors.


Regards,
Dariyoosh
Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580417 is a reply to message #580408] Sun, 24 March 2013 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

If you don't know how to format SQL or PL/SQL, learn it using SQL Formatter.

Regards
Michel
Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580422 is a reply to message #580409] Sun, 24 March 2013 06:07 Go to previous messageGo to next message
chankx91
Messages: 5
Registered: March 2013
Junior Member
Hi Dariyoosh

Sry actually I cant find the place where I read it. As u see here insert value is happens inside a for loop.)
(FETCH projMgrsCursor INTO projMgr;

I Use a Cursor for it. In this cursor they get the employee_id and insert the project-mangers to MICL_SUPERVISORS .There can be one or more project-mangers to that employee_ID. When trigger fired this for loop work. but the table I'm going to insert MICL_SUPERVISORS have a primary key for SUP_ID, so thats y this problem occur . as I figure out they
select micl_sup_id_seq.nextval into nSUPID from dual;
always insert the sam SUPID. In triggers we cant use commit, as it only commit for one transaction > In my case I have to increment the nSUPID

My question is is their any possible way to do this ?? Thank you in advance for any help you can provide. I'm sry for my bad English

FETCH projMgrsCursor INTO projMgr;
EXIT WHEN projMgrsCursor%NOTFOUND;

select micl_sup_id_seq.nextval into nSUPID from dual;

insert into MICL_SUPERVISORS (SUP_ID,ASSIGNED_DATE, ASSIGNED_BY_EMP_NO,AMOUNT_LIMIT,IS_OVVERRIDDEN , SUP_EMP_NO,RTD_EMP, EMP_NO)
VALUES ( nSUPID, (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, projMgr, NULL, :NEW.EMP_NO);

END LOOP;
CLOSE projMgrsCursor;
Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580424 is a reply to message #580422] Sun, 24 March 2013 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 24 March 2013 12:00
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


If you don't know how to format SQL or PL/SQL, learn it using SQL Formatter.

Regards
Michel

Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580425 is a reply to message #580417] Sun, 24 March 2013 06:10 Go to previous messageGo to next message
chankx91
Messages: 5
Registered: March 2013
Junior Member
Hi I,m sry ,

is their any way to edit and correct my mistake Smile
Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580426 is a reply to message #580422] Sun, 24 March 2013 06:11 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
The problem is that you know what you're talking about, but you assume that others also know like you, every detail about DDL/DML of your tables (which I'm afraid is not the case).

Please read what Michel said.

Regards,
Dariyoosh
Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580429 is a reply to message #580426] Sun, 24 March 2013 06:32 Go to previous messageGo to next message
chankx91
Messages: 5
Registered: March 2013
Junior Member
HI All

Here is the code

CREATE TABLE "M"."EMP_REPORT_TO" 
   (	"ID" NUMBER(19,0) NOT NULL ENABLE, 
	"ADDEDDATE" TIMESTAMP (6), 
	"ENTRYADDEDBY_EMP_NO" NUMBER(10,0), 
	"SUP_EMP_NO" NUMBER(10,0), 
	"EMP_NO" NUMBER(10,0), 
	 PRIMARY KEY ("ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "M"  ENABLE, 
	 CONSTRAINT "EMPREPORTTOENTRYADDEDBY_EMP_NO" FOREIGN KEY ("ENTRYADDEDBY_EMP_NO")
	  REFERENCES "M"."MASTER" ("EMP_NO") ENABLE, 
	 CONSTRAINT "FK_EMP_REPORT_TO_EMP_NO" FOREIGN KEY ("EMP_NO")
	  REFERENCES "M"."EMPLOYEE_MASTER" ("EMP_NO") ENABLE, 
	 CONSTRAINT "FK_EMP_REPORT_TO_SUP_EMP_NO" FOREIGN KEY ("SUP_EMP_NO")
	  REFERENCES "M"."MASTER" ("EMP_NO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "M" ;
 

  CREATE OR REPLACE TRIGGER "M"."INSERT_MICL_SUP_DETAILS" AFTER INSERT OR UPDATE OF "ID","SUP_EMP_NO","EMP_NO" ON "MIMAX"."EMP_REPORT_TO" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
DECLARE
  
  miclaim_supervisor_count number;
  employee_company_code VARCHAR2(10);
  employee_businessunit number;
  projMgr NUMBER;
  nSUPID NUMBER;
  
  cursor  projMgrsCursor is select b.BU_MEMBER_ID 
  from BU_MEMBER b, EMP_SUB_DIV s
  where s.EMP_NO = :NEW.EMP_NO
  and s.SUB_DIVISION_CODE = '0345' and s.DIV_CODE = '1010'
  and b.BU_ID IN (select BU_ID from BU_MEMBER where BU_MEMBER_ID = :NEW.EMP_NO);

BEGIN
     delete from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 0;
     select count(*) into miclaim_supervisor_count from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 1;
     select COMPANY_CODE into employee_company_code from  EMPLOYEE_MASTER where EMP_NO = :NEW.EMP_NO;
     projMgr := 0;
     
   
if (employee_company_code ='SOFT')then 
	
  OPEN  projMgrsCursor;
    LOOP
    select micl_sup_id_seq.nextval into nSUPID from dual;
   
    FETCH projMgrsCursor INTO projMgr;  
    EXIT WHEN projMgrsCursor%NOTFOUND;
   
    insert into  MICL_SUPERVISORS  (SUP_ID, ASSIGNED_DATE, ASSIGNED_BY_EMP_NO,AMOUNT_LIMIT,IS_OVVERRIDDEN , SUP_EMP_NO,RTD_EMP, EMP_NO)
    VALUES ( nSUPID, (SELECT SYSDATE FROM DUAL),  :NEW.ENTRYADDEDBY_EMP_NO, 3000,  0,   projMgr,   NULL,    :NEW.EMP_NO);
   
    END LOOP;	
    CLOSE projMgrsCursor;
else
    if(miclaim_supervisor_count IS NULL or miclaim_supervisor_count<1) then
    insert into MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, :NEW.SUP_EMP_NO, NULL,:NEW.EMP_NO);
    end if;
end if;

END;
/
ALTER TRIGGER "MIMAX"."INSERT_MICL_SUP_DETAILS" ENABLE;
 



trigger is fired to this table

 CREATE TABLE "M"."MICL_SUPERVISORS" 
   (	"SUP_ID" NUMBER(19,0) NOT NULL ENABLE, 
	"ASSIGNED_DATE" TIMESTAMP (6), 
	"ASSIGNED_BY_EMP_NO" NUMBER(10,0), 
	"AMOUNT_LIMIT" NUMBER(19,4), 
	"IS_OVVERRIDDEN" NUMBER(1,0) DEFAULT 0, 
	"SUP_EMP_NO" NUMBER(10,0), 
	"RTD_EMP" NUMBER(10,0), 
	"EMP_NO" NUMBER(10,0), 
	 PRIMARY KEY ("SUP_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "MIMAX"  ENABLE, 
	 CONSTRAINT "FK_MICL_SUPERVISORS_RTD_EMP" FOREIGN KEY ("RTD_EMP")
	  REFERENCES "M"."MASTER" ("EMP_NO") ENABLE, 
	 CONSTRAINT "FK_MICL_SUPERVISORS_EMP_NO" FOREIGN KEY ("EMP_NO")
	  REFERENCES "M"."MASTER" ("EMP_NO") ENABLE, 
	 CONSTRAINT "FK_MICL_SUPERVISORS_SUP_EMP_NO" FOREIGN KEY ("SUP_EMP_NO")
	  REFERENCES "M"."MASTER" ("EMP_NO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "M" ;




I hope that is what u guys asking

Error Message I'm getting is Quote:
ORA-00001: unique constraint violated


all this error happens inside the if clause
if (employee_company_code ='SOFT')then 
Else part is working smoothly


thanks alot

[Updated on: Sun, 24 March 2013 06:38]

Report message to a moderator

Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580433 is a reply to message #580429] Sun, 24 March 2013 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 24 March 2013 12:09
Michel Cadot wrote on Sun, 24 March 2013 12:00
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

If you don't know how to format SQL or PL/SQL, learn it using SQL Formatter.



And I add: do NOT post schema name and storage parameters in your test case, we have not the same ones.

Regards
Michel

Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580435 is a reply to message #580433] Sun, 24 March 2013 07:07 Go to previous messageGo to next message
chankx91
Messages: 5
Registered: March 2013
Junior Member
HI
Michel How Can I remove it ??
I'm sry Im a newbie and I made loads of mistakes Sad
Re: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval [message #580453 is a reply to message #580435] Sun, 24 March 2013 15:27 Go to previous message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could just edit it in any program that lets you edit text and delete the schema name. It's not hard.

As for your error - In some cases you are using the sequence to populate MICL_SUPERVISORS.SUP_ID and in some cases you're using the value from EMP_REPORT_TO.ID.
Why are you using two different sources for the primary key, doing so makes an ORA-00001 pretty much guaranteed.
Previous Topic: Using Variable inside a view
Next Topic: Function to insert
Goto Forum:
  


Current Time: Sat Aug 30 03:37:06 CDT 2014

Total time taken to generate the page: 0.05535 seconds