SQL: how to easily move deleted rows to another table with a trigger-CodePudding

CREATE TABLE TRASH_EMPLOYEE AS
    SELECT *
    FROM EMPLOYEE
    WHERE 1=0;

CREATE OR REPLACE TRIGGER TRG_02
BEFORE DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
    INSERT INTO TRASH_EMPLOYEE 
    VALUES (:OLD.EMP_ID, :OLD.EMP_NAME, :OLD.EMP_NO, :OLD.EMAIL,
            :OLD.PHONE, :OLD.DEPT_CODE, :OLD.JOB_CODE,
            :OLD.SAL_LEVEL, :OLD.SALARY, :OLD.BONUS,
            :OLD.MANAGER_ID, :OLD.HIRE_DATE, :OLD.ENT_DATE, :OLD.ENT_YN);
--       (
--            SELECT * 
--            FROM EMPLOYEE
--            WHERE EMP_ID = :OLD.EMP_ID 
--            );
--         
END;
/
DELETE EMPLOYEE
    WHERE EMP_NAME='Anne';
SELECT * FROM TRASH_EMPLOYEE;

I am trying to move any rows that get deleted to another table using a trigger. But the commented out code doesn't work for me.

Is there any way other than what I did above (writing all the column)?

Thanks in advance!

CodePudding user response：

It doesn't work because it tries to select from table which is just being modified, i.e. it is "mutating".

The right way to do it is what you already probably know - INSERT values you're deleting, referenced by the :old pseudorecord.