ORA-04091:表xx_xx正在变异，触发器/功能可能看不到它 [英] ORA-04091: table xx_xx is mutating, trigger/function may not see it

问题描述

所以我必须创建一个触发器，该触发器将对名为"passengerlist1"的表所做的更改记录到名为"logs"的附加表中.

So i have to create a trigger which will log changes made to a table called 'passengerlist1' into an extra table called 'logs'.

日志表:

create table logs (
  p_name varchar(255),
  p_surname varchar(255),
  f_id number,
  time_stamp timestamp
);

Passengerlist1表具有以下属性: FLIGHTID，PERSONID，SEATNUMBER.

Passengerlist1 table has the following attributes: FLIGHTID, PERSONID, SEATNUMBER.

还有另一个名为PERSON1的表，具有以下属性: PERSONID，GIVENNAME，FAMILYNAME，DATEOFBIRTH.这是我需要从中选择一个人的给定名称和姓氏并将其存储到该表中的表.如果对"passengerlist1"表进行了更改，则使用"logs"表.从"passengerlist1"表中，我只需要选择FLIGHTID并将其存储到"logs"表中.

There is another table involed called PERSON1 with attributes: PERSONID, GIVENNAME, FAMILYNAME, DATEOFBIRTH. This is the table from where i need to select the givenname and familyname of a person and store it into the 'logs' table in case changes are made to 'passengerlist1' table. From 'passengerlist1' table i only need to select the FLIGHTID and store it into the 'logs' table.

因此，这就是我的触发器:

So with this out of the way here is my trigger:

CREATE OR REPLACE TRIGGER log_changes 
  AFTER INSERT OR UPDATE OR DELETE
  ON passengerlist1
  FOR EACH ROW

DECLARE
 t_name varchar2(255);
 t_surname varchar2(255);
BEGIN
  BEGIN

   IF DELETING THEN
    SELECT PERSON1.GIVENNAME INTO T_NAME FROM 
      PERSON1 JOIN PASSENGERLIST1 
      ON PERSON1.PERSONID = PASSENGERLIST1.PERSONID
      WHERE PASSENGERLIST1.FLIGHTID = :OLD.FLIGHTID;

    SELECT PERSON1.FAMILYNAME INTO T_SURNAME FROM 
      PERSON1 JOIN PASSENGERLIST1 
      ON PERSON1.PERSONID = PASSENGERLIST1.PERSONID
      WHERE PASSENGERLIST1.FLIGHTID = :OLD.FLIGHTID;

    INSERT INTO LOGS VALUES (T_NAME, T_SURNAME, :OLD.FLIGHTID, SYSDATE);
   END IF;

   IF UPDATING THEN

    SELECT PERSON1.GIVENNAME INTO T_NAME FROM 
      PERSON1 JOIN PASSENGERLIST1 
      ON PERSON1.PERSONID = PASSENGERLIST1.PERSONID
      WHERE PASSENGERLIST1.FLIGHTID = :NEW.FLIGHTID;

     SELECT PERSON1.FAMILYNAME INTO T_SURNAME FROM 
      PERSON1 JOIN PASSENGERLIST1 
      ON PERSON1.PERSONID = PASSENGERLIST1.PERSONID
      WHERE PASSENGERLIST1.FLIGHTID = :NEW.FLIGHTID;

    INSERT INTO LOGS VALUES (T_NAME, T_SURNAME, :NEW.FLIGHTID, SYSDATE);
   END IF;

   IF INSERTING THEN
      SELECT PERSON1.GIVENNAME INTO T_NAME FROM 
      PERSON1 JOIN PASSENGERLIST1 
      ON PERSON1.PERSONID = PASSENGERLIST1.PERSONID
      WHERE PASSENGERLIST1.FLIGHTID = :NEW.FLIGHTID;

     SELECT PERSON1.FAMILYNAME INTO T_SURNAME FROM 
      PERSON1 JOIN PASSENGERLIST1 
      ON PERSON1.PERSONID = PASSENGERLIST1.PERSONID
      WHERE PASSENGERLIST1.FLIGHTID = :NEW.FLIGHTID;

      INSERT INTO LOGS VALUES (T_NAME, T_SURNAME, :NEW.FLIGHTID, SYSDATE);
   END IF;
END;
END;
/

现在使用以下匿名块:

begin
 delete from passengerlist1 where flightid = 1;
end;
/

要对passengerlist1表进行一些更改，因为我将该触发器定义为 AFTER 触发器，我希望更改首先生效，然后该触发器将触发插入到日志"中桌子.但是相反，我得到了这个错误:

to make some changes to passengerlist1 table, being that i defined this trigger as an AFTER trigger, i was expecting the changes to take effect first, then the trigger would fire insert into the 'logs' table. But instead i get this error:

ORA-04091:表xx_passengerlist1正在突变，触发器/函数可能看不到

我在做什么错?预先谢谢你.

What am i doing wrong? Thank you in advance.

推荐答案

您无法在触发器中查询变异表.
看到这个: https://docs.oracle.com/cd/B19306_01/appdev .102/b14251/adfns_triggers.htm

You can't query a mutating table in the trigger.
See this: https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

突变表的触发限制

变异表是由UPDATE修改的表， DELETE或INSERT语句，或者可能由表更新的表 DELETE CASCADE约束的效果.

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

发出触发语句的会话无法查询或 修改变异表.此限制可防止触发 看到一组不一致的数据.

The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

此限制适用于所有使用FOR EACH ROW的触发器 条款.不考虑在INSTEAD OF触发器中修改的视图 变异.

This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.

当触发器遇到变异表时，会发生运行时错误， 触发主体和触发语句的效果会发生变化 返回，并将控制权返回给用户或应用程序.

When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.

请尝试以下代码:

create or replace 
TRIGGER log_changes 
  AFTER INSERT OR UPDATE OR DELETE
  ON passengerlist1
  FOR EACH ROW

DECLARE
 t_name varchar2(255);
 t_surname varchar2(255);
BEGIN
   IF DELETING THEN
    SELECT PERSON1.GIVENNAME, PERSON1.FAMILYNAME INTO T_NAME,  T_SURNAME
    FROM PERSON1
    WHERE PERSON1.PERSONID = :OLD.PERSONID;

    INSERT INTO LOGS VALUES (T_NAME, T_SURNAME, :OLD.FLIGHTID, SYSDATE);
   END IF;

   IF UPDATING OR INSERTING THEN

     SELECT PERSON1.GIVENNAME,  PERSON1.FAMILYNAME  INTO T_NAME, T_SURNAME 
     FROM PERSON1 
     WHERE PERSON1.PERSONID = :NEW.PERSONID;

     INSERT INTO LOGS VALUES (T_NAME, T_SURNAME, :NEW.FLIGHTID, SYSDATE);
   END IF;
END;
/

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