Oracle trigger error ORA-04091

问题:

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!

回答1:

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.

回答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;