how to use triggers to inherit values from another "parent" record in same table

Question

I feel like this is one of those "if you're careful you can do it" scenarios that Oracle just doesn't want to let me do.

My problem is that I have a single configuration table that I want to enable inheritance via Triggers. Think an Employee table with a SUPERVISOR ID column, and 'inherited' SUPERVISOR NAME that self populates if the ID is changed.

I'd like to do a simple self-lookup to capture a value from another row at time of INS/UPD. But Oracle is rejecting as a mutating trigger error.

My code is essentially:

TRIGGER UPD_CHILD_RECORD
BEFORE INSERT OR UPDATE
ON MYSCHEMA.FAKE_EMPLOYEE
FOR EACH ROW
WHEN (NEW.SUPERVISOR_ID IS NOT NULL)
BEGIN
IF INSERTING OR UPDATING
THEN
    :NEW.SUPERVISOR_NAME = (
        SELECT MAX(NAME)
        FROM MYSCHEMA.FAKE_EMPLOYEE
        WHERE EMPLOYEE_ID = :NEW.SUPERVISOR_ID
    );
END IF;
END UPD_CHILD_RECORD
;

thanks.

Answer 1

This is a normal behavior. Oracle protects you from inconsistent data that you may get accessing a table which is already being updated.

Imagine this scenario.
You submit two update statements and have a trigger that selects from that same table. Let's assume that the first statement is successfully applied and the data gets changed. Now it's time for the second statement. What output would you expect from the select statement in the trigger? Should it return data as it was before the first update, or should it include the changes made? You probably think that Oracle should return the new data. But first, Oracle does not really know your intentions, and second, that would mean that your query is dependent on row order, which contradicts the relational algebra.

The solution for your problem is quite simple. You do not need the SUPERVISOR_NAME column at all. To get supervisor's name, simply join the table with itself and get the desired result, something like:

select t1.ID, t1.SUPERVISOR_ID, t2.NAME from FAKE_EMPLOYEE t1
    left join FAKE_EMPLOYEE t2 on t1.SUPERVISOR_ID = t2.ID;