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
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.