Oracle Trigger Error Ora-04091

Oracle trigger error ORA-04091

There are several issues here:

1. Oracle does not allow you to perform a SELECT/INSERT/UPDATE/DELETE against a table within a row trigger defined on that table or any code called from such a trigger, which is why an error occurred at run time. There are ways to work around this - for example, you can read my answers to this question and this question - but in general you will have to avoid accessing the table on which a row trigger is defined from within the trigger.

2. The calculation which is being performed in this trigger is what is referred to as business logic and should not be performed in a trigger. Putting logic such as this in a trigger, no matter how convenient it may seem to be, will end up being very confusing to anyone who has to maintain this code because the value of BILANZ is changed where someone who is reading the application code's INSERT or UPDATE statement can't see it. This calculation should be performed in the INSERT or UPDATE statement, not in a trigger. It considered good practice to define a procedure to perform INSERT/UPDATE/DELETE operations on a table so that all such calculations can be captured in one place, instead of being spread out throughout your code base.

3. Within a BEFORE ROW trigger you can modify the values of the fields in the :NEW row variable to change values before they're written to the database. There are times that this is acceptable, such as when setting columns which track when and by whom a row was last changed, but in general it's considered a bad idea.

Best of luck.

Table is mutating, trigger/function may not see it (stopping an average grade from dropping below 2.5)

I think you can fix this by rewriting this as a before trigger, rather than an after trigger. However, this might be a little complicated for inserts and deletes. The idea is:

CREATE OR REPLACE TRIGGER stopChange
    BEFORE UPDATE OR INSERT OR DELETE ON taking
    REFERENCING OLD AS old
    NEW AS new
    FOR EACH ROW
DECLARE

grd_avg taking.grade%TYPE;

BEGIN
    SELECT (SUM(grade) - oldgrade + new.grade) / count(*)
    INTO grd_avg
    FROM taking
    WHERE studentnum = :new.studentnum
    AND schedulenum = :new.schedulenum
    AND semester = :new.semester;

    IF grd_avg < 2.5 THEN
        new.grade = old.grade
    END IF;
END;  

ORA-04091: table is mutating, trigger/function may not see it, ORA-06512:, ORA-06512: at SYS.DBMS_SQL, line 1721

Don't create a new transaction with a separate update. Use the :new pseudo table in a before trigger to modify the column as part of the same transaction.

create or replace trigger trg_job_date 
before insert or update
on job
for each row 
begin
    :new.job_last_update := sysdate;
end;

See documentation, here: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-triggers.html#GUID-4F93F21E-BA7F-4378-87E6-46A8E4C03287

BEFORE TRIGGER causes ORA-04091

This error indicates that you cannot query the table on which the trigger was fired within the trigger itself.

But in your use case, it seems like you do not need to query the table. If you want to access the current value of column SHIPDATE on the record that is about to be updated, you can simply use :OLD.SHIPDATE.

So something like:

BEFORE UPDATE ON ORD
FOR EACH ROW
BEGIN
    IF (:old.SHIPDATE IS NOT NULL) THEN
        RAISE_APPLICATION_ERROR(20121,'ORDER already on the way!!!');
    END IF;

END;

---

Related Topics