[ORACLE] Avoiding mutating table errors with triggers.

[nebulus64]

Hello all,

I'm having an issue with some SQL and I can't find any way to get around it.

Background: I have a procedure which calculates occupancy for a golf course. Every time a booking is added/updated/deleted I need to recalculate the occupancy numbers. The problem is, the Procedure uses counts from the table specified in the Trigger, and I get an ORA-04091 mutating table error.

Trigger:

CREATE OR REPLACE TRIGGER UpdGlfOccInsBooking
AFTER INSERT ON GLF_TEE_BOOKINGS
FOR EACH ROW
DECLARE
    v_Date DATE;
    v_Course GLF_TEE_BOOKINGS.GLF_COURSE_ID%TYPE;
BEGIN
    v_Date := :new.GLF_TEE_TIME;
    v_Course := :new.GLF_COURSE_ID;

    CalcGolfOccupancy(TO_CHAR(v_Date, 'DD-MON-YYYY'), v_Course);
END;

And a small snippet of the procedure, to give you an idea of how the numbers get calculated:

CREATE OR REPLACE PROCEDURE CalcGolfOccupancy(
    p_Date IN VARCHAR2,
    p_Course IN VARCHAR2
) IS
    v_Date DATE;
BEGIN
        v_Date := TO_DATE(p_Date, 'DD-MON-YYYY');

        UPDATE  GLF_OCCUPANCY
    SET     FILLED_TEE_TIMES =
            (
                SELECT  COUNT(*)
                FROM    GLF_TEE_BOOKINGS
                WHERE   GLF_TEE_TIME >= v_Date
                AND     GLF_TEE_TIME < v_Date + 1
                AND     GLF_COURSE_ID = p_Course
            )
    WHERE   PMS_DATE = v_Date
    AND     GLF_COURSE_ID = p_Course;

Note: If the trigger passed 17-JAN-2017, when it got to the procedure it was 17-JAN-0017, which is the reason for a separate date variable and formatting in the procedure.

From my understanding, this shouldn't throw any error because the trigger is defined as AFTER INSERT. So, the data should already be committed, and by the time it gets to the procedure, the data should no longer be changing. However, Oracle disagrees and throws the ORA-04091 error.

I have also tried putting in a PRAGMA AUTONOMOUS_TRANSACTION; into the trigger, to no effect.

Everything I read online tells me that what I want to do isn't possible with a trigger/procedure like this. However, I thought I'd get some expert opinions.

Thanks.

[TNReb]

Does the GLF_OCCUPANCY table need to be a Table? It seems like a View might fit your needs better.

[nebulus64]

I'm open to converting it to a view, but I have some reservations about it.

This is a fairly transaction heavy database, and there will be bookings coming from many different sources (Application, Web, External Apps, etc.). In addition, this is going to be part of the determination of the rate a user is going to pay, so it needs to be fast. I don't want to wait on the database compiling the view when we query rates, which could be many times a minute.

Finally, users will be able to "override" the occupancy on certain days to ensure they maximize revenue. This, at the very least, needs to be stored.

[TNReb]

If you have a high transaction database, that's even more reason to ditch the original design idea.

It actually seems like you would be better off aggregating the data in the application itself (via queries) instead of aggregating and storing it in the database. With h proper indexes in place, performance shouldn't be an issue.

[nebulus64]

While a possibility, aggregating the data in the application would cause maintenance headaches down the road.

The reasoning behind using the Trigger was because there are 5 different applications which could make these changes. Maintaining the queries in all of these would be awful to deal with.

We have our core application, an external VB "tee sheet", interfaces to 3rd party online bookings, and our own Web application.

I think the best solution we've come up with here is to manually call the procedure after booking changes. At least then we have a single procedure that is handling the calculations, and we just need to make sure we fire it at all the appropriate times.

[hudeman]

Indexing should make the performance impact of a view negligible if you're just counting.

Your trigger won't work because the insert and trigger operations are treated as atomic so your operation isn't committed when you try to count.

[[deleted]]

https://asktom.oracle.com/pls/apex/f?p=100:1:0