title: PL/SQL (INSERT/UPDATE) unique constraint violated error in trigger due to sequencel.nextval
body: <p>I want insert/update  records to another table (MICL_SUPERVISORS) using Trigger (pl/sql oracle 10g). </p>
  
  <p>When trigger fired it is giving an error as </p>
  
  <blockquote>
    <p>ORA-00001: unique constraint violated.</p>
  </blockquote>
  
  <p>I know it happens because I want to add <code>SUPID</code> from sequence</p>
  
  <pre><code>Select micl_sup_id_seq.nextval into nSUPID from dual  
  </code></pre>
  
  <p>And this is happening inside a loop. </p>
  
  <p>SUPID column is primary key in my table( MICL_SUPERVISOR). So I can't drop that constraint. </p>
  
  <p>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 </p>
  
  <pre><code>SELECT MAX((SUP_ID)+1 from micl_sup_id_seq
  </code></pre>
  
  <p>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?</p>
  
  <p>Here is the code (it all happening inside <strong>if clause</strong> else part is working Fine. Pls note that I have use a cursor , inside open cursor all this happen)</p>
  
  <pre><code>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
                                       , SYSDATE
                                       , :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&lt;1) THEN
        INSERT INTO micl_supervisors VALUES (:NEW.ID
                                            , SYSDATE
                                            , :NEW.entryaddedby_emp_no
                                            , 3000
                                            , 0
                                            , :NEW.sup_emp_no
                                            , NULL
                                            , :NEW.emp_no);
      END IF;
    END IF;
  END;
  /
  </code></pre>
  
  <p>If anything unclear ask me I'll explain furthermore about this scenario , I hope anyone will help to solve this problem</p>