删除行时的Oracle错误：.正在发生变异，触发器/函数可能看不到

Question

尝试从assessment_flaw中删除一行将导致以下错误：

ORA-04091:表IN120032.ASSESSMENT_FLAW正在变异，触发器/函数可能看不到ORA-06512: at "IN120032.GEN_IDS_ASSESSMENT"，行22 ORA-04088:执行触发器'IN120032.GEN_IDS_ASSESSMENT‘ORA-06512: at "IN120032.GEN_IDS_ASSESSMENT_FLAW"，第12行ORA-04088:在执行触发器'IN120032.GEN_IDS_ASSESSMENT_FLAW’时出错。

我不理解的是，将行插入到该表中可以很好地工作，而删除它们显然不起作用。

下面是我的sql脚本：

-- SEQUENCE

--drop sequence ids;
create sequence ids;

-- TABLES
drop view vehicle_view;
drop view assessment_view;
drop view assessment_flaw_view;
drop table assessment_flaw;
drop table flaw;
drop table assessment;
drop table vehicle;
drop table customer;

create table customer (
    id number(4) primary key,
    name varchar(50),
    street varchar(100),
    zip varchar(5),
    city varchar(100),
    phone varchar(15)
);

create table vehicle (
    id number(4) primary key,
    customer_id number(4) references customer,
    brand varchar(100),
    model varchar(100),
    year number(4),
    mileage number(10),
    weight number(4),
    brakepower number(4)
);

create view vehicle_view as
    select vehicle.id as "Id",
        customer.name as "Customer",
        vehicle.brand as "Brand",
        vehicle.model as "Model",
        vehicle.year as "Year",
        vehicle.mileage as "Mileage",
        vehicle.weight as "Weight",
        vehicle.brakepower as "Brakepower"
    from vehicle, customer
    where vehicle.customer_id = customer.id;

create table assessment (
    id number(4) primary key,
    vehicle_id number(4) references vehicle,
    datum date default sysdate,
    editor varchar(100),
    result number(4) default 0,
    flaws number(4) default 0,
    brakepower number(4,2) default 0
);

create view assessment_view as
    select assessment.id as "Id",
        vehicle_view."Customer" as "Customer",
        vehicle_view."Brand" as "Brand",
        vehicle_view."Model" as "Model",
        assessment.datum as "Date",
        assessment.editor as "Editor",
        assessment.result as "Result",
        assessment.flaws as "Flaws",
        assessment.brakepower as "Brakepower"
    from assessment, vehicle_view
    where assessment.vehicle_id = vehicle_view."Id";

create table flaw (
    id number(4) primary key,
    description varchar(100),
    rank number(4)
);

create table assessment_flaw (
    id number(4) primary key,
    assessment_id number(4) references assessment,
    flaw_id number(4) references flaw
);


create view assessment_flaw_view as
    select assessment_flaw.id as "Id",
        assessment_view."Customer" as "Customer",
        assessment_view."Brand" as "Brand",
        assessment_view."Model" as "Model",
        assessment_view."Editor" as "Editor",
        assessment_view."Result" as "Result",
        assessment_view."Flaws" as "Flaws",
        flaw.description as "Flaw",
        flaw.rank as "Flaw rank"
    from assessment_view, flaw, assessment_flaw
    where assessment_flaw.assessment_id = assessment_view."Id" and assessment_flaw.flaw_id = flaw.id;

-- TRIGGERS
create or replace trigger gen_ids_customer
before insert or update of id on customer
for each row
declare
    n number;
begin
    select ids.nextval into n from dual;
    if inserting then
        :new.id := n;
    elsif updating and :new.id != :old.id then
        raise_application_error(-20002, 'ID ist unveraenderlich konstant !!!');
    end if;
end;
/

create or replace trigger gen_ids_vehicle
before insert or update of id on vehicle
for each row
declare
    n number;
begin
    select ids.nextval into n from dual;
    if inserting then
        :new.id := n;
    elsif updating and :new.id != :old.id then
        raise_application_error(-20002, 'ID ist unveraenderlich konstant !!!');
    end if;
end;
/

create or replace trigger gen_ids_assessment
before insert or update on assessment
for each row
declare
    n number;
    bp assessment.brakepower%type;
    tmp number;
begin
    if inserting then
        select ids.nextval into n from dual;
        -- id
        :new.id := n;

        -- brakepower
        select ROUND(vehicle.weight / vehicle.brakepower,2) into bp from vehicle where vehicle.id = :new.vehicle_id;
        :new.brakepower := bp;
    elsif updating then
        if :new.id != :old.id then
            raise_application_error(-20002, 'ID ist unveraenderlich konstant !!!');
        end if;

        -- ok?
        dbms_output.put_line('gen_assessment_result before ok?');
        :new.result := 1;
        select COUNT(*) into tmp from assessment_flaw, flaw where assessment_flaw.assessment_id = :new.id and assessment_flaw.flaw_id = flaw.id and flaw.rank = 1;
        if tmp > 2 then
            :new.result := -1;
        end if;
        select COUNT(*) into tmp from assessment_flaw, flaw where assessment_flaw.assessment_id = :new.id and assessment_flaw.flaw_id = flaw.id and flaw.rank != 1;
        if tmp > 0  then
            :new.result := -1;
        end if;
        dbms_output.put_line('gen_assessment_result after ok?');
    end if;
end;
/

create or replace trigger gen_ids_flaw
before insert or update of id on flaw
for each row
declare
    n number;
begin
    select ids.nextval into n from dual;
    if inserting then
        :new.id := n;
    elsif updating and :new.id != :old.id then
        raise_application_error(-20002, 'ID ist unveraenderlich konstant !!!');
    end if;
end;
/

create or replace trigger gen_ids_assessment_flaw
before insert or delete or update on assessment_flaw
for each row
declare
    n number;
begin
    if inserting then
        select ids.nextval into n from dual;
        :new.id := n;

        dbms_output.put_line('flaws-count++ on assessment ' || :new.assessment_id);
        update assessment set assessment.flaws = assessment.flaws + 1 where assessment.id = :new.assessment_id;
    elsif deleting then
        dbms_output.put_line('flaws-count-- on assessment ' || :new.assessment_id);
        update assessment set assessment.flaws = assessment.flaws - 1 where assessment.id = :old.assessment_id;
    elsif updating then
        raise_application_error(-20002, 'Entitaet ist unveraenderlich konstant !!!');
    end if;
end;
/

-- INSERTS

insert into flaw(description, rank) values('Achseneinstellung nicht korrekt', 2);
insert into flaw(description, rank) values('Lichterfehlfunktion', 1);
insert into flaw(description, rank) values('Bremsscheiben abgenutzt', 3);
insert into flaw(description, rank) values('Becker auf der Windschutzscheibe', 1);

insert into customer(name, street, zip, city, phone) values('Simon Lammer', 'Str. 4', 4563, 'Micheldorf', '0680/12345431');
insert into customer(name, street, zip, city, phone) values('David Chen', 'Sonstige-Str. 42', 1234, 'Linz', '1234/9876555');
insert into customer(name, street, zip, city, phone) values('Jakob Woegerbauer', 'Gute-Str. 2', 9876, 'Schoenstadt', '9876/1234567');

insert into vehicle(customer_id, brand, model, year, mileage, weight, brakepower) values((select id from customer where customer.name like 'Simon Lammer'), 'Tesla', 'Model S', 2012, 5000, 2100, 1700);
insert into vehicle(customer_id, brand, model, year, mileage, weight, brakepower) values((select id from customer where customer.name like 'Simon Lammer'), 'Jeep', 'Wrangler', 1993, 1750645, 1480, 1300);
insert into vehicle(customer_id, brand, model, year, mileage, weight, brakepower) values((select id from customer where customer.name like 'David Chen'), 'Golf', 'GTI', 2001, 349256, 810, 530);
insert into vehicle(customer_id, brand, model, year, mileage, weight, brakepower) values((select id from customer where customer.name like 'Jakob Woegerbauer'), 'Aston Martin', 'V8 Vantage', 2007, 491274, 1695, 1200);

insert into assessment(vehicle_id, editor) values((select vehicle.id from vehicle, customer where vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Model S'), 'in120032');
insert into assessment_flaw(assessment_id, flaw_id) values((select assessment.id from assessment, vehicle, customer where assessment.vehicle_id = vehicle.id and vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Model S'), (select flaw.id from flaw where flaw.description like 'Lichterfehlfunktion'));
insert into assessment_flaw(assessment_id, flaw_id) values((select assessment.id from assessment, vehicle, customer where assessment.vehicle_id = vehicle.id and vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Model S'), (select flaw.id from flaw where flaw.description like 'Lichterfehlfunktion'));
delete from assessment_flaw where assessment_flaw.assessment_id = (select assessment.id from assessment, vehicle, customer where assessment.vehicle_id = vehicle.id and vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Model S' fetch next 1 rows only);

insert into assessment(vehicle_id, editor) values((select vehicle.id from vehicle, customer where vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Wrangler'), 'in120032');
insert into assessment_flaw(assessment_id, flaw_id) values((select assessment.id from assessment, vehicle, customer where assessment.vehicle_id = vehicle.id and vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Wrangler'), (select flaw.id from flaw where flaw.description like 'Bremsscheiben abgenutzt'));
insert into assessment_flaw(assessment_id, flaw_id) values((select assessment.id from assessment, vehicle, customer where assessment.vehicle_id = vehicle.id and vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Wrangler'), (select flaw.id from flaw where flaw.description like 'Achseneinstellung nicht korrekt'));

-- SELECTS
select * from customer
--    where customer.name like 'Simon Lammer'
;

select * from vehicle_view;

select vehicle.id from vehicle, customer where vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Model S';

select * from assessment_view;

select * from flaw;

select * from assessment_flaw_view;

更新1-为了澄清我的问题:我知道下面一行是导致错误的原因：

 delete from assessment_flaw where assessment_flaw.assessment_id = (select assessment.id from assessment, vehicle, customer where assessment.vehicle_id = vehicle.id and vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Model S' fetch next 1 rows only);

据我所知，这个错误是由这个删除引起的。

1. triggering gen_ids_assessment_flaw which in turn
2. updates assessment and thus 
3. triggers gen_ids_assessment of which the update part
4. queries assessment_flaw
-> mutating table.

问题是:为什么插入不导致相同的错误？

insert into assessment_flaw(assessment_id, flaw_id) values((select assessment.id from assessment, vehicle, customer where assessment.vehicle_id = vehicle.id and vehicle.customer_id = customer.id and customer.name like 'Simon Lammer' and vehicle.model = 'Model S'), (select flaw.id from flaw where flaw.description like 'Lichterfehlfunktion'));

在执行时，is插入是

1. triggering gen_ids_assessment_flaw which in turn
2. updates assessment and thus 
3. triggers gen_ids_assessment of which the update part
4. queries assessment_flaw
-> NO mutating table.

以下是两个重要的触发因素：

create or replace trigger gen_ids_assessment
before insert or update on assessment
for each row
declare
    n number;
    bp assessment.brakepower%type;
    tmp number;
begin
    if inserting then
        select ids.nextval into n from dual;
        -- id
        :new.id := n;

        -- brakepower
        select ROUND(vehicle.weight / vehicle.brakepower,2) into bp from vehicle where vehicle.id = :new.vehicle_id;
        :new.brakepower := bp;
    elsif updating then
        if :new.id != :old.id then
            raise_application_error(-20002, 'ID ist unveraenderlich konstant !!!');
        end if;

        -- ok?
        dbms_output.put_line('gen_assessment_result before ok?');
        :new.result := 1;
        select COUNT(*) into tmp from assessment_flaw, flaw where assessment_flaw.assessment_id = :new.id and assessment_flaw.flaw_id = flaw.id and flaw.rank = 1;
        if tmp > 2 then
            :new.result := -1;
        end if;
        select COUNT(*) into tmp from assessment_flaw, flaw where assessment_flaw.assessment_id = :new.id and assessment_flaw.flaw_id = flaw.id and flaw.rank != 1;
        if tmp > 0  then
            :new.result := -1;
        end if;
        dbms_output.put_line('gen_assessment_result after ok?');
    end if;
end;
/

create or replace trigger gen_ids_assessment_flaw
before insert or delete or update on assessment_flaw
for each row
declare
    n number;
begin
    if inserting then
        select ids.nextval into n from dual;
        :new.id := n;

        dbms_output.put_line('flaws-count++ on assessment ' || :new.assessment_id);
        update assessment set assessment.flaws = assessment.flaws + 1 where assessment.id = :new.assessment_id;
    elsif deleting then
        dbms_output.put_line('flaws-count-- on assessment ' || :new.assessment_id);
        update assessment set assessment.flaws = assessment.flaws - 1 where assessment.id = :old.assessment_id;
    elsif updating then
        raise_application_error(-20002, 'Entitaet ist unveraenderlich konstant !!!');
    end if;
end;
/

Answer 1

您会得到错误，因为：

1 You delete assessment_flaw.
2 Trigger on assessment_flaw updates assessment.
3 Trigger on assessment queries assessment_flaw  <<< mutating table.

无法查询要从触发器中插入、更新和删除的表。