Oracle trigger error ORA-04091

Question

I get an error (ORA-04091: table DBPROJEKT_AKTIENDEPOT.AKTIE is mutating, trigger/function may not see it) when executing my trigger:

CREATE OR REPLACE TRIGGER Aktien_Bilanz_Berechnung
AFTER 
INSERT OR UPDATE OF  TAGESKURS
OR INSERT OR UPDATE OF  WERT_BEIM_EINKAUF
ON AKTIE
FOR EACH ROW
DECLARE
bfr number;
Begin
bfr := :new.TAGESKURS - :new.WERT_BEIM_EINKAUF;
UPDATE AKTIE
SET BILANZ = TAGESKURS - WERT_BEIM_EINKAUF;
IF bfr < -50
THEN
DBMS_OUTPUT.PUT_LINE('ACHTUNG: The value (Nr: '||:new.AKTIEN_NR||') is very low!');
END IF;
END;

I want to check the value "BILANZ" after calculating it, wether it is under -50. Do you have any idea why this error is thrown?

Thanks for any help!

score 2 · Answer 1 · edited Jun 21 '18 at 10:58

2

You are modifying the table with the trigger. Use a before update trigger:

CREATE OR REPLACE TRIGGER Aktien_Bilanz_Berechnung
BEFORE INSERT OR UPDATE OF TAGESKURS OR INSERT OR UPDATE OF  WERT_BEIM_EINKAUF
ON AKTIE
FOR EACH ROW
DECLARE
    v_bfr number;
BEGIN
    v_bfr := :new.TAGESKURS - :new.WERT_BEIM_EINKAUF;
    :new.BILANZ := v_bfr;
    IF v_bfr < -50 THEN
      Raise_Application_Error(-20456,'ACHTUNG: The value (Nr: '|| :new.AKTIEN_NR || ') is very low!');
    END IF;
END;

edited Jun 21 '18 at 10:58

Barbaros Özhan

59,113
10
31
55

answered Jun 21 '18 at 10:47

Gordon Linoff

1,242,037
58
646
786

1

I replaced `dbms_output.put_line` with `Raise_Application_Error` so that the error can be seen from everywhere, even from an application. Got rid of useless `dual`. +1 – Barbaros Özhan Jun 21 '18 at 11:01
1

@BarbarosÖzhan . . . Those are fine. For some reason, I'm in the habit of using `into` when I assign values into columns in triggers in Oracle. Don't know why. It might be an archaic habit. – Gordon Linoff Jun 21 '18 at 11:36

score 2 · Accepted Answer · answered Jun 21 '18 at 11:36

There are several issues here:

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.
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.
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.

Oracle trigger error ORA-04091

2 Answers2

Linked

Related