Hi;
I have a table in my work schema with 5 million records. There is a simple trigger on the table that checks some business rules.
This trigger was designed years ago so the developers created a mirror table that has the same 5+ million records.
This was done to perform validations and checks as part of the business rules and if they pass, then records get inserted into the main table.
We couldn't have done those checks in the main table itself, as inserting records into the main table depends upon carrying out these checks in the mirror table and inserting into the main table if checks were successful.
If the business rules fail then application error is thrown.
Due to performance degradation it was recommended that the mirror table be dropped. I removed the mirror table and rewrote the trigger.
During testing when I do an insert statement like the one given below:
insert into the tab1 select * from tab1 where column_name = 'specific value' ,
I get a mutating error. Please note that note that tab1 is table the trigger is enabled for.
I have rewritten the trigger as a compound trigger but when I test using the above insert statement I still get a mutating error.
Please tell me how I can resolve this error.
Table structure and the code in trigger is given below. Database version is Oracle 12c
Thanks in advance.
+++++++++++++++++++++++++++++++++
CREATE
TABLE DELEGATION
(
PRO_ID_NUM VARCHAR2(15 BYTE) NOT NULL
,
WL_ITEM_NUM NUMBER(3, 0) NOT NULL
,
ORG_SEQ_NUM NUMBER(5, 0) NOT NULL
,
EMPLOYEE_NUM VARCHAR2(9 BYTE) NOT NULL
,
ROLE_CODE VARCHAR2(4 BYTE) NOT NULL
,
BEGIN_DATE DATE NOT NULL
,
END_DATE DATE
,
TIME_TO_DATE NUMBER(8, 2)
,
REP_FLAG VARCHAR2(1 BYTE)
)
;
Composite primary key Column number 1,2,3,4,5,6
+++++++++++++++++++++++++++++++++
CREATE
OR REPLACE TRIGGER delegation_trg1 FOR INSERT OR
UPDATE OR
DELETE ON delegation compound TRIGGER
------------
last_end_date DATE :=NULL;
v_error_message VARCHAR2(255);
DELETING_IS_BAD EXCEPTION;
v_dml_type VARCHAR2(1);
------------
/* retrieve employees previous Delegations for same
project, wli and organization */
CURSOR get_prev_Dlgtns_info
IS
SELECT end_date
FROM delegation
WHERE PRO_ID_NUM =
:new.PRO_ID_NUM
AND WL_ITEM_NUM =
:new.WL_ITEM_NUM
AND
employee_num
= :new.employee_num
AND ORG_SEQ_NUM = :new.ORG_SEQ_NUM
ORDER BY begin_date,
end_date;
--------------
BEFORE STATEMENT
IS
BEGIN
NULL;
END
BEFORE STATEMENT;
-------------
before
EACH row
IS
BEGIN
IF inserting THEN
OPEN get_prev_dlgtns_info;
FETCH get_prev_dlgtns_info INTO last_end_date;
/* no record found on initial fetch will prevent loop
from being entered */
WHILE get_prev_dlgtns_info%FOUND
LOOP
/* found an open dlelegation, raise exception */
IF last_end_date IS NULL THEN
RAISE_APPLICATION_ERROR(-20800,'Previous delegation not closed');
END IF;
--
FETCH get_prev_dlgtns_info INTO last_end_date;
--
END LOOP;
/* last record retrieve will have an end date */
IF last_end_date IS NOT NULL THEN
/* not in chronological order, raise exception */
IF :new.begin_date < last_end_date THEN
RAISE_APPLICATION_ERROR(-20801,'Delegations must be in chronological' || '
order. Begin date must be > or = end date of last delegation.');
END IF;
--
END IF;
--
CLOSE get_prev_Dlgtns_info;
elsif deleting THEN
raise_application_error ('-20002','DELEGATIONS may not be deleted.');
END IF;
END
before EACH row;
---------------------------------------------------------
END;
/