CODEWITHSUNDEEP
sqloracle-databasetriggerscursorplsqldeveloper
Lara Dax
Lara Dax

Reputation: 151

How to write a trigger to move data I want to delete from a table to antother before deleting it

I wrote this trigger which monitor a table called employees , and when deleting an employee from that table the trigger should fire , and copy the employee we want to delete from the table employees and put in another table called deleted_employees , but when I put the following sql statement delete from employees where employee_id = 100 SQL Error says :

Error starting at line 17 in command:
delete from employees where employee_id = 100
Error report:
SQL Error: ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child
                      record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

CREATE OR REPLACE TRIGGER EMPLOYEE_DELETED 
  BEFORE DELETE ON EMPLOYEES 
DECLARE
  CURSOR CUR_EMP IS
    SELECT  EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , 
            PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY ,
            COMMISSION_PCT ,  MANAGER_ID , DEPARTMENT_ID
      FROM EMPLOYEES;
  EMP_REC CUR_EMP%ROWTYPE;
BEGIN
  OPEN CUR_EMP;

  while(CUR_EMP%FOUND) LOOP
    FETCH CUR_EMP INTO EMP_REC;
    INSERT  INTO deleted_employees
      (EMPLOYEE_ID , FIRST_NAME , LAST_NAME ,
       EMAIL ,
       PHONE_NUMBER , HIRE_DATE ,job_id , salary ,
       COMMISSION_PCT ,  MANAGER_ID , DEPARTMENT_ID)
    VALUES
      (EMP_REC.EMPLOYEE_ID ,EMP_REC.FIRST_NAME ,EMP_REC.LAST_NAME ,
       EMP_REC.EMAIL , EMP_REC.PHONE_NUMBER , EMP_REC.HIRE_DATE ,
       EMP_REC.JOB_ID , EMP_REC.SALARY , EMP_REC.COMMISSION_PCT ,
       EMP_REC.MANAGER_ID , EMP_REC.DEPARTMENT_ID);
  END LOOP;

  CLOSE CUR_EMP;
END;

I don't know how to test the trigger , any ideas ?!

Upvotes: 0

Views: 3731

Answers (1)

pratik garg
pratik garg

Reputation: 3342

One thing I observed that you are, by mistake, inserting all the records from EMPLOYEE table to DELETED_EMPLOYEES.

I think you want to insert the employee detail into deleted_employee table which is currently in delete action.

For this in Oracle you can using :OLD keyword to refer the current running record. well if you want to delete related chile records as well .. then you can do in this trigger itself without any problem -

CREATE OR REPLACE TRIGGER EMPLOYEE_DELETED 
BEFORE DELETE ON EMPLOYEES 
FOR EACH ROW
DECLARE
BEGIN
INSERT  INTO deleted_employees
(
 EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL ,
 PHONE_NUMBER , HIRE_DATE ,job_id , salary ,
 COMMISSION_PCT ,  MANAGER_ID , DEPARTMENT_ID
)
VALUES
(:OLD.EMPLOYEE_ID ,:OLD.FIRST_NAME ,:OLD.LAST_NAME , :OLD.EMAIL ,
 :OLD.PHONE_NUMBER , :OLD.HIRE_DATE , :OLD.JOB_ID , :OLD.SALARY ,
 :OLD.COMMISSION_PCT ,  :OLD.MANAGER_ID , :OLD.DEPARTMENT_ID);

DELETE from DEPARTMENT where EMPLOYEE_ID = :OLD.EMPLOYEE_ID; -- If you are deleting child record then you will not get ORA:02292 error

END;

Just edit delete part in this trigger as per your database.

for this you need to first check for the key constraint DEPT_MGR_FK. I guessed that this key is present in DEPARTMENT table on EMPLOYEE_ID column.

so check for this key and change the second last line and then compile.

Upvotes: 1

Related Questions