PLSQL触发错误. “正在变异，触发器/功能可能看不到它". ORA-04091 [英] PLSQL Trigger Error. "is mutating, trigger/function may not see it" ORA-04091

问题描述

当我在Oracle数据库项目中声明触发器时，出现错误，提示:

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'

基本上，我正在创建一个用于管理棋盘游戏联赛的学术项目.因此，有一个游戏表和一个联赛表.还有一个League_Games表，该表指示联赛中是否有可用的游戏(即，联赛中的玩家是否玩此游戏).联赛可以决定停止拥有"一个游戏，然后将其从League_Games表中删除.但是，当发生这种情况时，我想运行一个触发器，以检查是否还有一些联赛在使用该游戏，如果没有，请从游戏"表中删除该特定游戏的条目.'

我的触发器是这样的:

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;
/

因此，当我从Leage_Games删除条目时，会出现错误.此触发器有什么问题?

解决方案

您正尝试从要更新的同一张表中查询数据(在这种情况下，请删除..).您无法执行此操作，因为它是更改"

请继续阅读有关变异触发器的更多信息.汤姆·凯特(Tom Kyte)在这里提供了出色的分步指南:

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

和此处有关触发器的麻烦"的另一个博客条目:

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

基本上，您应该重新考虑逻辑并考虑不使用触发器;)

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?

解决方案

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 ;)

这篇关于PLSQL触发错误. “正在变异，触发器/功能可能看不到它". ORA-04091的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！