Databases
StackOverflow2013
Postsdbo
POHow to write a trigger to move data I want to delete from a table to antother before deleting it
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to write a trigger to move data I want to delete from a table to antother before deleting it
    text
    copied!<p>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 :</p> <pre><code>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; </code></pre> <p>I don't know how to test the trigger , any ideas ?!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload