3

I'm creating 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;/

Whenver I run the trigger by updating a row in Collectionitems I get squirrel returning this :

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

SQLState:  42000
ErrorCode: 4098
Position: 7

Executing SQL statement:

SELECT * FROM User_Errors;

Generates 1 error:

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

I've read quite a few posts with others having the same problem but haven't found a solution yet, any ideas?

Nakilon
  • 34,866
  • 14
  • 107
  • 142
Will Andrew
  • 693
  • 1
  • 10
  • 29
  • This is never going to work. You're going to end up with a mutating table exception. Your actual problem is twofold. You should reference the new values as `:newrow.` and you do need a carriage return / line feed before '/'. – Ben May 06 '12 at 15:38
  • Thanks for the response, I've recreated the mutating table exception, please could you explain why this is so? – Will Andrew May 06 '12 at 15:53

2 Answers2

4

The actual reason for this failing to compile is twofold.

  1. You should reference the new values as :newrow.
  2. You do need a carriage return and/or line feed before /.

However, this trigger is never going to work. You're going to end up with a mutating table exception, ORA-04091: table name is mutating, trigger/function may not see it. This is because you're referencing the table you're updating in a row level trigger. Simply put Oracle ensures a read-consistent view of the data.

In the trigger, you're in the middle of updating Collectionitems. Selecting from this table at the same time, in the same session, there are two possible states; updated and not updated. To ensure that your view is read-consistent Oracle raises this error.

In your particular case you might want to consider a materialized view rather than a trigger.

Further Reading:

Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
0

Put the / at the end of the trigger on a line to itself.

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
  • You recreated the trigger with the `/` on a separate line and it still gives you that error? Are you creating the trigger in the `CA0513` schema? – Luke Woodward May 06 '12 at 15:45
  • What about `SHOW ERRORS TRIGGER CA0513.ITEMAVGRATINGTRIGGER`? – Luke Woodward May 06 '12 at 15:51
  • returns "Error: ORA-00900: invalid SQL statement" – Will Andrew May 06 '12 at 15:56
  • Ah, that's because `SHOW ERRORS` is specific to SQL*Plus, but you're using Squirrel. Sorry. Anyway, if you've reproduced the table-mutating error, then I'd guess you must have got rid of the 'invalid trigger' error message. – Luke Woodward May 06 '12 at 16:05
  • that would explain it, thanks and yes it's gone but now we're not sure why it is producing that exception! – Will Andrew May 06 '12 at 16:06