插入触发器导致变异表错误后

创建表语句：

CREATE TABLE Client(
    nclient INT NOT NULL, 
    nom VARCHAR(45) NOT NULL UNIQUE,
    plafond NUMERIC(10,2) DEFAULT 0,
    CHECK (plafond>=0),
    CONSTRAINT PK_Client PRIMARY KEY (nclient)
  );

CREATE TABLE Compte( 
    ncompte INT NOT NULL, 
    solde NUMERIC(10,2),
    ouvert NUMERIC(1) DEFAULT 1,
    nbessais INT,
    code Numeric(4),
    nclient NUMERIC(4),
    CONSTRAINT PK_Compte PRIMARY KEY (ncompte),
    CONSTRAINT FK_Compte_nclient_Client FOREIGN KEY (nclient) REFERENCES Client(nclient),
    CONSTRAINT CK_solde CHECK (solde>=0),
    CONSTRAINT CK_ouvert CHECK(ouvert between 0 and 1),
    CONSTRAINT CK_code CHECK (code>0)
  );

CREATE TABLE Operation( 
    noperation INT NOT NULL, 
    dateoperation DATE Default SYSDATE,
    codepropose NUMERIC(4),
    montant NUMERIC(10,2),
    ncompte INT,
    CONSTRAINT PK_Operation PRIMARY KEY (noperation),
    CONSTRAINT FK_Ope_ncompte_Compte FOREIGN KEY (ncompte) REFERENCES Compte(ncompte),
    CONSTRAINT CK_codepropose CHECK (codepropose>=0),
    CONSTRAINT CK_montant CHECK (montant>=0)
  );

CREATE TABLE Historique(
    nhistorique INT NOT NULL, 
    dateoperation DATE DEFAULT SYSDATE,
    montant NUMERIC(10,2),
    ncompte INT,
    CONSTRAINT PK_Historique PRIMARY KEY (nhistorique)
  );

CREATE TABLE Incident(
    nincident INT NOT NULL, 
    message VARCHAR(45),
    noperation INT,
    CONSTRAINT PK_Incident PRIMARY KEY (nincident),
    CONSTRAINT FK_Inc_noperation_Operation FOREIGN KEY (noperation) REFERENCES Operation(noperation)
  );

触发码：

create or replace TRIGGER TRG_OPERATION_CARTE
  AFTER INSERT OR UPDATE ON Operation
  FOR EACH ROW
  DECLARE
    solde NUMBER := 0;
    ouvert NUMBER := 0;
    essai NUMBER := 0;
    code NUMBER := 0;
    plafondActuel NUMBER := 0;
    decouvertAuth NUMBER := 0;
  BEGIN
    SELECT SUM(montant) INTO plafondActuel FROM Operation WHERE dateoperation >= SYSDATE - 7 AND ncompte = :NEW.ncompte;
    SELECT plafond INTO decouvertAuth FROM Client Cl LEFT JOIN Compte Cp ON Cl.nclient = Cp.nclient WHERE Cp.ncompte = :NEW.ncompte;
    SELECT solde, ouvert, nbessais, code
      INTO solde, ouvert, essai, code
      FROM Compte
      WHERE Compte.ncompte = :NEW.ncompte;    
    IF ouvert = 0 THEN
      INSERT INTO Incident (message,noperation) values ('Compte bloqué',:NEW.noperation);
    ELSE
      IF :NEW.codepropose = code THEN
        IF (solde + decouvertAuth) > :NEW.montant THEN
          IF (plafondActuel + :NEW.montant) > 300 THEN
            INSERT INTO Incident (message,noperation) values ('Lopération dépasse le plafond authorisé',:NEW.noperation);
          ELSE
            INSERT INTO Historique (montant,ncompte) values (:NEW.montant,:NEW.ncompte);
          END IF;
        ELSE
          INSERT INTO Incident (message,noperation) values ('Lopération dépasse le découvert authorisé',:NEW.noperation);
        END IF;
      ELSE
        essai := essai + 1;
        UPDATE Compte SET nbessais = essai WHERE Compte.ncompte = :NEW.ncompte;
        IF essai >=3 THEN
          UPDATE Compte SET ouvert = 0 WHERE Compte.ncompte = :NEW.ncompte;
          INSERT INTO Incident (message,noperation) values ('Compte bloqué, 3 tentatives échouées',:NEW.noperation);
        END IF;
      END IF;
    END IF;
  END;

我很确定算法和触发器是正确的，但是我有一个错误：

原因：触发器（或此语句中引用的用户定义的plsql函数）试图查看（或修改）被触发该语句的语句正在修改的表。

行动：重写触发器（或函数），使其不读取该表。

这是一些测试，除了最后一次插入，一切正常：

INSERT INTO Client (nom) values ('abderrahmane');
INSERT INTO Client (nom) values ('ben');
Select * FROM Client;
INSERT INTO Compte (solde,nclient) values (200,1);
INSERT INTO Compte (nclient,code) values (2,2403);
INSERT INTO Compte (solde,nclient) values (2300,3);
SELECT * FROM Compte;
INSERT INTO Operation (montant,ncompte) values (200,2);
INSERT INTO Operation (codepropose,ncompte) values (2010,2);
INSERT INTO Operation (ncompte) values (1);
INSERT INTO Operation (codepropose,montant,ncompte) values (2020,20,1);
INSERT INTO Operation (codepropose,montant,ncompte,dateoperation) values (2030,30,2,'01/01/2001');
INSERT INTO Operation (codepropose,montant,ncompte) values (2030,30,3);
SELECT * FROM Operation;  
INSERT INTO Historique (montant,ncompte) values (200,2);
INSERT INTO Historique (dateoperation,ncompte) values ('15/04/16',2);
INSERT INTO Historique (ncompte) values (1);
INSERT INTO Historique (dateoperation,montant,ncompte) values ('15/04/16',20,1);
INSERT INTO Historique (dateoperation,montant,ncompte) values ('15/04/16',30,3);
SELECT * FROM Historique;
INSERT INTO Incident (message,noperation) values ('Incident 1',2);
INSERT INTO Incident (message,noperation) values ('Incident 2',8);
INSERT INTO Incident (message,noperation) values ('Incident 3',1);
SELECT * FROM Incident; 
UPDATE Compte SET ouvert = 0 WHERE nclient = 2;
INSERT INTO Operation (montant,ncompte,codepropose) values (200,2,2403);
SELECT * FROM Incident;