"Error: ORA-04098: trigger" CA0513.ITEMAVGRATINGTRIGGER "is invalid and did not retest"

I create a trigger:

CREATE OR REPLACE TRIGGER ItemAvgRatingTrigger
AFTER INSERT OR UPDATE OF rating ON Collectionitems
REFERENCING NEW AS newRow
FOR EACH ROW
BEGIN
    UPDATE Item
    SET rating = (SELECT AVG(Collectionitems.rating) FROM Collectionitems, Item WHERE
        Collectionitems.itemid = newRow.itemid AND newRow.itemid = Item.itemid)
    WHERE Item.itemid = newRow.itemid;
END ItemAvgRatingTrigger;/

When I start the trigger by updating the line in Collectionitems, I get a squirrel returning this:

Error: ORA-04098: trigger 'CA0513.ITEMAVGRATINGTRIGGER' is invalid and failed re-validation

SQLState:  42000
ErrorCode: 4098
Position: 7

Executing an SQL statement:

SELECT * FROM User_Errors;

It creates 1 error:

PLS-00103: Encountered the symbol "/" The symbol "/" was ignored./n

I read a few posts with the same problems, but still haven't found a solution, any ideas?

+3
source share
2 answers

The actual reason for compilation failure is twofold.

  • You must refer to the new values ​​as :newrow.
  • You need to return the carriage and / or line to /.

. , ORA-04091: table name is mutating, trigger/function may not see it. , , . , Oracle .

Collectionitems. , ; . , , Oracle .

, .

:

+4

/ .

0
source

All Articles