PL SQL: missing row inserted in an TRIGGER with AFTER INSERT

Question

I'll try to explain my problem, linked to Trigger functionalities. I want to add a row in a table, focusing our attention on a specific number in a column, called "VOTO" (VOTE), for example:

ID  UTENTE_ID  DATA       COMMENTO     ISBN            VOTO
50  423573     29-LUG-14  CAPOLAVORO!  9788804508359   5

After this inserting, it will be activated a Trigger like this:

create or replace TRIGGER CREATE_COMMENT
AFTER INSERT OR UPDATE ON RECENSIONI
FOR EACH ROW

pragma autonomous_transaction;

BEGIN
    UPDATE LIBRI
    SET VOTOPROD_MEDIA=(SELECT AVG(VOTO) FROM RECENSIONI WHERE ISBN=:NEW.ISBN GROUP BY ISBN)
    WHERE ISBN=:NEW.ISBN;

END;

The idea behind this trigger is explained here: I had to add a row with a VOTE, then, it will be selected a row into another table called LIBRI (books) with a parameter called VOTOPROD_MEDIA (Average Rate for a book) where will be stored the Average Rate (AVG) of all votes of the comments related to that book.

EXAMPLE BEFORE TRIGGER:

TABLE COMMENTS:

ID  USER_ID  DATE       COMMENT     ISBN            VOTE
50  423573   29-LUG-14  CAPOLAVORO! 9788804508359   5

TABLE BOOKS:

ISBN           AVERAGE_VOTE
9788804508359  5

EXAMPLE OF WHAT I WOULD DO AFTER A TRIGGER:

TABLE COMMENTS:

ID  USER_ID  DATE       COMMENT     ISBN            VOTE
50  423573   29-LUG-14  CAPOLAVORO! 9788804508359   5
51  423574   29-LUG-14  BAD BOOK!   9788804508359   1

TABLE BOOKS:

ISBN           AVERAGE_VOTE
9788804508359  3

3 is the "AVG" value of 5 and 1.

Unfortunately, it doesn't work. The last inserting seems to do not affect that update. In fact, while the execution of this trigger the table COMMENTS, I suppose, is not yet updated with the new row inserted, so, when it tries to make the AVG, the VOTE in Table Books remains to 5, and not changes to 3.

Even though I use an "AFTER INSERT" I think that during the Trigger the table "RECENSIONI" remains in this state:

ID  USER_ID  DATE       COMMENT     ISBN            VOTE
50  423573   29-LUG-14  CAPOLAVORO! 9788804508359   5

I'm almost sure about it because the same "problem" occurs in another trigger. Well, I wish to "avoid" this structural problem in an automatic way, creating a "way" to make this calculation after the insert or the update, but REALLY inserting that row.

How can I do this?

You're using an autonomous transaction to avoid a mutating table issue; but that means the trigger can't see the uncommitted data in the table (which you are in the middle of inserting). Trying to maintain this value with a trigger is going to serialise the inserts anyway though, at best. Have you considered a view that calculates the average on the fly instead; or even a materialised view if that doesn't perform well enough? — Alex Poole
That's the problem... how I can execute the trigger without mutating table issue? I'll try using views, thanks :) — dogmaxpeppe
But honestly I don't have idea how to create a view with a column generated in that way... — dogmaxpeppe

Alex Poole Alex Poole · Accepted Answer · 2014-07-29T18:40:48

You can create a simple view with something like:

CREATE OR REPLACE VIEW V_LIBRI AS
SELECT L.ISBN, AVG(R.VOTO) AS VOTO_MEDIO
FROM LIBRI L
LEFT JOIN RECENSIONI R
ON R.ISBN = L.ISBN
GROUP BY L.ISBN;

SQL Fiddle demo. No triggers required. For large amounts of data where the view doesn't perform well you might consider a materialised view instead, but that would have pros and cons (e.g. requiring more storage).

PL SQL: missing row inserted in an TRIGGER with AFTER INSERT

2 Answers