oracle - ORA-04091 : table xx_xx is mutating, 触发器/函数可能看不到它

Question

所以我必须创建一个触发器，将对名为“passengerlist1”的表所做的更改记录到名为“logs”的额外表中。

日志表:

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

Passengerlist1 表具有以下属性:FLIGHTID、PERSONID、SEATNUMBER。

还有另一个名为 PERSON1 的表，其属性为:PERSONID、GIVENNAME、FAMILYNAME、DATEOFBIRTH。 在该表中，我需要选择一个人的名字和姓氏并将其存储到'logs' 表，以防对 'passengerlist1' 表进行更改。从“passengerlist1”表中，我只需选择 FLIGHTID 并将其存储到“logs”表中。

因此，解决了这个问题，这就是我的触发器:

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

现在使用以下匿名 block :

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

要对passengerlist1表进行一些更改，因为我将此触发器定义为AFTER触发器，我希望更改首先生效，然后触发器将触发插入到“日志”中 table 。但我却收到此错误:

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

我做错了什么？预先感谢您。

Answer 1

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

Trigger Restrictions on Mutating Tables

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.

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