PL/SQL (INSERT/UPDATE) unique constraint violated error in trigger due to sequencel.nextval

Question

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

When trigger fired it is giving an error as

ORA-00001: unique constraint violated.

I know it happens 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 can't 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?

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
                                     , 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<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;
/

If anything unclear ask me I'll explain furthermore about this scenario , I hope anyone will help to solve this problem

Answer 1

What other constraints are present on the table? It's more likely that you're running into a constraint error other the sequence, which you are fixated upon.

Due to this Error I did a small research and found out we cannot use seq.nextval inside a trigger.

I don't know where you read that, but that's absolutely false. I've used seq.nextval for many of my audit triggers/tables, and it works fine.

Query all_constraints (or user_constraints) with table name micl_supervisors - like so

SELECT *
FROM   user_constraints
WHERE  table_name = 'MICL_SUPERVISORS' 

and update the question or check with what data you're trying to insert.