Mutating table trigger error upon deleting from a table

Question

I have these table definitions

CREATE TABLE EMPLOYEE(
    EmployeeID NUMBER(4),
    Name VARCHAR2(20),
    Hiredate DATE NOT NULL,
    Gender VARCHAR(1),
    DateOfBirth DATE NOT NULL,
    Salary NUMBER(8,2),
    Commission NUMBER(8, 2),
    DName VARCHAR(20),
    PName VARCHAR(20),
    Phone NUMBER(8) NOT NULL,
    GLetter VARCHAR(1),
    CONSTRAINT EMPLOYEE_EMPLOYEEID_PK PRIMARY KEY(EMPLOYEEID),
    CONSTRAINT EMPLOYEE_DNAME_FK FOREIGN KEY(DName) REFERENCES DEPARTMENT(DName) ON DELETE CASCADE,
    CONSTRAINT EMPLOYEE_PNAME_FK FOREIGN KEY(PName) REFERENCES POSITION(PName) ON DELETE CASCADE,
    CONSTRAINT EMPLOYEE_GLETTER_FK FOREIGN KEY(GLetter) REFERENCES GRADE(GLetter) ON DELETE CASCADE,
    CONSTRAINT GENDER_CK CHECK (Gender='M' or Gender='F')
);

CREATE TABLE LOGIN(
    Username VARCHAR(20),
    Password VARCHAR(20),
    EmployeeID NUMBER(4),
    CONSTRAINT LOGIN_USERNAME_PK PRIMARY KEY(Username),
    CONSTRAINT LOGIN_EMPLOYEEID_FK FOREIGN KEY(EmployeeID) REFERENCES EMPLOYEE(EmployeeID) ON DELETE CASCADE
);

CREATE SEQUENCE TRANSACTION_SEQ START WITH 6;

CREATE TABLE TRANSACTION(
    TransactionID NUMBER(4) DEFAULT TRANSACTION_SEQ.NEXTVAL,
    TransactionDate DATE, 
    Username VARCHAR(20), 
    EmployeeID NUMBER(4),
    CONSTRAINT TRANSACTION_TRANSACTIONID_PK PRIMARY KEY(TransactionID),
    CONSTRAINT TRANSACTION_USERNAME_FK FOREIGN KEY(Username) REFERENCES LOGIN(Username) ON DELETE CASCADE,
    CONSTRAINT TRANSACTION_EMPLOYEEID_FK FOREIGN KEY(EmployeeID) REFERENCES EMPLOYEE(EmployeeID) ON DELETE CASCADE
);

and this trigger

CREATE OR REPLACE TRIGGER EMPLOYEE_TRANSACTION
    BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEE
    FOR EACH ROW
DECLARE
    ID NUMBER(4);
    USR VARCHAR(20);
BEGIN
    SELECT LOWER(USER)
    INTO USR
    FROM DUAL;
    
    SELECT EMPLOYEEID
    INTO ID
    FROM LOGIN
    WHERE USERNAME = USR;
    
    INSERT INTO TRANSACTION VALUES(DEFAULT, SYSDATE, USR, ID);
END;
/

my problem arises when trying to delete an employee. basically, the trigger finds out the employeeid of the user who is making the changes and inserts it and other values to the transaction table. i get this error:

Error report -
ORA-04091: table ---.LOGIN is mutating, trigger/function may not see it
ORA-06512: at "---.EMPLOYEE_TRANSACTION", line 9
ORA-04088: error during execution of trigger '---.EMPLOYEE_TRANSACTION'

is oracle taking into account the possibility that i am deleteing an employee whose id (which is going to be deleted from table LOGIN) is that of the oracle USER?

any solutions? thank you very much!

Thanks for posting code. Unfortunately, you don't have a reproducible test case. First off, you're referencing tables that don't exist in the code you posted. If I comment out those extraneous constraints, the next problem is that you can't insert data into your tables. If you try to insert the first row in employee, the trigger fires and throws an error because there is no associated login row. But I can't create the login row because there is a foreign key constraint to employee. — Justin Cave
I can catch the no_data_found error in the trigger but then the insert into transaction fails because there is no row in employee yet to reference. I can keep modifying your code to try to replicate the problem by, for example, changing the trigger to only fire on deletes but I might be changing something in a way that violates what it is that you're trying to do. — Justin Cave
My guess is that you want something like this fiddle as a reproducible test case dbfiddle.uk/… — Justin Cave
hello @JustinCave ! thanks for your comments. yes, you wouldn't be able to insert rows in employee if you don't have some within already before compiling the trigger. in my code, i have inserted some rows that pertain to "admins", and wrote the trigger after the lines defining them. — Luyw

Justin Cave Justin Cave · Accepted Answer · 2021-04-07T20:08:09

If we assume that this fiddle is a reproducible test case that demonstrates your problem (note that I had to make several changes to your code in order to be able to do things like inserting the test data), the issue is that the foreign key constraint on the login table is defined to do a cascade delete (on delete cascade). That means that when you are deleting a row from employee, login will be mutating (Oracle is in the middle of deleting the child row). Thus you can't query it from within a trigger on employee.

Depending on exactly what you are hoping to accomplish, you have a few options

Don't query the login table and don't store the employeeID in the transaction table. If you have the username that performed the operation in the transaction table, you can always look up their employeeID.
Don't use triggers to populate the transaction table. If you have a stored procedure that deletes an employee, it makes more sense for that procedure to do the work of logging the transaction.
Don't define your constraints as on delete cascade. That would mean, though, that you'd need to delete the login row prior to deleting the employee row. Here is an example where we remove the on delete cascade from the LOGIN_EMPLOYEEID_FK constraint and add a statement to delete the associated login row.

Mutating table trigger error upon deleting from a table

1 Answers