Ytukyg

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!

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.

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;