SQL - Count number of rows in a table before updating it

Question

Im doing a college project on Oracle SQL and I keep getting the error "table (...) is mutating, trigger/function may not see it" when I try to update a table. My participateTeams table has 3 columns (eventName varchar(), teamId number, teamPlace number).

The Objective here is that when I update the teamPlace of a given team in an event, the place MUST be lower or equal than the number of participant teams in that same event.

My trigger is as follows:

Before update on participateTeams
for each row
Declare participants number;
Begin
     select count(*) into participants from participateteams where :New.eventName = participateteams.eventName;

        if(:NEW.teamplace>participants) then
            RAISE_APPLICATION_ERROR(-20250,'O lugar da equipa é inválido');
        end if;
End;
/

From what I've researched it's because I'm trying to read the same table that called the trigger. I have also have tried exporting the select part of the code into a function, but the issue persists.

The participants in the event are the teams theirselves and not the individuals that form those same teams. Example: If team A, team B and team C participate in an event E, the count for that same event E should be 3.

Any tips? Thank you.

Answer 1

The mutating table error is just a pain. And what you are trying to do is rather tricky. Here is one approach:

1. Add a column to teams with the count of participants.
2. Maintain this column with insert/update, and delete triggers on participateteams.
3. Write a user-defined function to fetch the count for a given team.
4. Add a check constraint in participateteams using the user-defined function.

Answer 2

The logically correct trigger for what you want to achieve is this :-

CREATE OR REPLACE TRIGGER TRIG_CHK
AFTER INSERT OR UPDATE OF teamPlace ON participateteams
FOR EACH ROW
DECLARE
participants NUMBER;
BEGIN
 select count(*) into participants from participateteams where :New.eventName = 
 participateteams.eventName;
    if(:NEW.teamplace>participants) then
        RAISE_APPLICATION_ERROR(-20250,'O lugar da equipa é inválido');
    end if;
End;    
update participateteams set teamPlace = 2 where eventName = 'B';

But when it gives Mutating table error when trying to update table participateteams,to solve this tricky situation , what I did is :-

step1 :Declare the table columns needed in a package header

CREATE OR REPLACE PACKAGE PKG_TEAMS AS
v_eventName participateteams.eventName%type;
v_teamPlace participateteams.teamPlace%type;
end;

step2 : Initialize the variables in a row level trigger

CREATE OR REPLACE TRIGGER TRG_row
AFTER INSERT OR UPDATE OF teamPlace
ON participateteams
FOR EACH ROW
BEGIN
PKG_TEAMS.v_eventName := :NEW.eventName;
PKG_TEAMS.v_teamPlace := :NEW.teamPlace;
END;

step3 :Now ,rather than using :NEW pseudo columns, using globally initialized variables

  CREATE OR REPLACE TRIGGER TRG_stmt
AFTER INSERT OR UPDATE OF teamPlace ON participateteams
DECLARE 
v_participants NUMBER;
BEGIN
SELECT COUNT(*) INTO v_participants FROM participateteams
WHERE eventName = PKG_TEAMS.v_eventName;
 if(PKG_TEAMS.v_teamPlace>v_participants) then
            RAISE_APPLICATION_ERROR(-20250,'CANNOT UPDATE,AGAINST RULES');
        end if;
End;