i am trying to insert data into history table , if the record is getting deleted . Basically i have three tables EMP, EMP_INFO and EMP_HIST.
EMP is master table which has primary key (emp_no)
EMP_INFO is a child table with emp_no as foriegn key. If i delete the record from EMP , information from both tables need to be stored into EMP_HIST like audit information. I tried writing one trigger but it is giving me mutating error. I was reading on compound trigger but there is no example on delete. Is there a work around . Appreciate if someone can give example.
[code]
CREATE TABLE EMP (EMP_NO VARCHAR2(10),EMP_NAME VARCHAR2(10));
CREATE TABLE EMP_INFO (EMP_NO VARCHAR2(10),CF_LABOR_ID VARCHAR2(12));
create table emp_hist(emp_no varchar2(11),e_labor varchar2(100));
ALTER TABLE EMP ADD (
CONSTRAINT EMP1_CPK
PRIMARY KEY
(EMP_NO)
);
ALTER TABLE EMP_INFO ADD (
CONSTRAINT EMP2_CFK
FOREIGN KEY (EMP_NO)
REFERENCES EMP(EMP_NO)
ON DELETE CASCADE
ENABLE NOVALIDATE);
INSERT INTO EMP(EMP_NO,EMP_NAME) VALUES ( 'A','ALPHA');
INSERT INTO EMP(EMP_NO,EMP_NAME) VALUES ( 'B','BETA');
INSERT INTO EMP_INFO(EMP_NO,CF_LABOR_ID) VALUES ('A','10001');
COMMIT
CREATE OR REPLACE TRIGGER trg_before_emp_delete
BEFORE DELETE
ON emp
FOR EACH ROW
DECLARE
CURSOR c1
IS
SELECT cf_labor_id
FROM emp_info
WHERE emp_no = :old.emp_no;
m_labor emp_info.cf_labor_id%TYPE;
BEGIN
OPEN C1;
FETCH C1 INTO M_LABOR;
CLOSE C1;
INSERT INTO EMP_HIST (EMP_NO, e_labor)
VALUES (:OLD.EMP_NO, M_LABOR);
END;
delete from emp where emp_no= 'A';
table IFSAPP.EMP_INFO is mutating, trigger/function may not see it
ORA-06512: at "IFSAPP.TRG_BEFORE_EMP_DELETE", line 4
ORA-06512: at "IFSAPP.TRG_BEFORE_EMP_DELETE", line 10
ORA-04088: error during execution of trigger 'IFSAPP.TRG_BEFORE_EMP_DELETE'
[/code]