3

When I declare a trigger in my Oracle database project, I'm getting an error, which says:

ERROR at line 1:
ORA-04091: table MIHAL277.LEAGUE_GAMES is mutating, trigger/function may not
see it
ORA-06512: at "MIHAL277.DELETE_UNUSED_GAME", line 4
ORA-04088: error during execution of trigger 'MB358996.DELETE_UNUSED_GAME'

Basically I'm creating an academic project for managing boardgame leagues. So there is a Game table and a League table. There's also a League_Games table, which indicates whether a game is available in a League (i.e if the players in the League play this game). A League can decide to stop 'owning' a game and then it's deleted from the League_Games table. But when it happens I want to run a trigger, which checks out if there are still some Leagues that use the game and if not - delete the entry for the particular game from the Game table.'

My trigger goes like this:

CREATE OR REPLACE TRIGGER delete_unused_game
BEFORE DELETE ON League_Games
FOR EACH ROW
DECLARE
   deleted_game_count integer;
BEGIN
    SELECT COUNT(*) 
    INTO deleted_game_count 
    FROM League_Games 
    WHERE Game_ID = :OLD.Game_ID;
    IF deleted_game_count = 0 THEN
        DELETE FROM Game
        WHERE ID = :OLD.Game_ID;
    END IF;
END;
/

So when I delete an entry from Leage_Games, the error appears. What is wrong with this trigger?

mihal277
  • 126
  • 1
  • 7
  • There are two ways to work around this issue - use a statement trigger or use a compound trigger, and of the two the compound trigger will probably have better performance. See my answer to [this question](http://stackoverflow.com/questions/29489951/oracle-trigger-after-insert-or-delete) for an example of using a compound trigger. Best of luck. – Bob Jarvis - Слава Україні Jan 22 '16 at 17:30

1 Answers1

1

You are trying to query data from the same table you are updating (well, deleting in this case ..). You can't do that, since it's "Mutating"

Read up on mutating triggers more .. Tom Kyte offers an excellent step by step here:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:290416059674

and another blog entry here on "The trouble with triggers":

http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html

Basically, you should revisit your logic and consider doing it without using triggers ;)

Ditto
  • 3,256
  • 1
  • 14
  • 28
  • Thank you. Just to be sure: it's wrong even if I changed to 'AFTER DELETE'? – mihal277 Jan 22 '16 at 16:56
  • yeah, as soon as you're doing that aggregate (ie COUNT) on the entire table, you're going to hit mutating trigger ... that's kinda the "textbook" way to show mutating triggers ;) Read those links I gave, you should understand it much better afterwards. – Ditto Jan 22 '16 at 16:57
  • I sure will. Thank you for your help. – mihal277 Jan 22 '16 at 17:01
  • If you are insisting on doing it trigger based, to avoid this error you need to switch to a statement-level trigger. Remove the "for each row" designation and validate that there are no games for which there are now no league_games, and delete them. It is less efficient as you wind up having to check all games each time you delete a single record, but if the data set isn't huge and the tables are indexed it should still be pretty quick. – Michael Broughton Jan 22 '16 at 17:18