I have a compound trigger which three DML statements in it. And one of my job is failing because of this trigger with the error “ORA-12838: Object cannot be read/changed after parallel change error”. Since commit cant be done in a trigger, what will be the best option to fix this issue. The error occurs after the insert statement.
CREATE OR REPLACE TRIGGER dup_ct FOR INSERT ON test1 COMPOUND TRIGGER TYPE a_records IS TABLE OF test1%ROWTYPE INDEX BY PLS_INTEGER; new_records a_records := a_records (); rows_inserted PLS_INTEGER := 0; v_dup PLS_INTEGER; BEFORE EACH ROW IS BEGIN rows_inserted := rows_inserted + 1; new_records (rows_inserted).class_id := :NEW.class_id; new_records (rows_inserted).mark := :NEW.mark; new_records (rows_inserted).std := :NEW.std; new_records (rows_inserted).res_date := :NEW.res_date; new_records (rows_inserted).exam_date := :NEW.exam_date; END BEFORE EACH ROW; AFTER STATEMENT IS BEGIN FOR j IN 1 .. rows_inserted LOOP SELECT COUNT (*) INTO v_dup FROM test1 WHERE mark = new_records (j).mark AND std = new_records (j).std AND res_date = new_records (j).res_Date AND exam_date = new_records (j).exam_date; IF v_dup > 1 THEN INSERT INTO test2 ( class_id, mark, std , res_date, exam_date) VALUES ( new_records(j).class_id, new_records (j).mark, new_records (j).std , new_records(j).res_date, new_records (j).exam_date); delete from test1 select * from test2; END IF; END LOOP; new_records.DELETE; rows_inserted := 0; END AFTER STATEMENT; END dup_ct; /
This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.