试图从assessment_flaw中删除行会导致以下错误：
ORA-04091:表in120032.评估漏洞正在突变，触发器/函数可能看不到ORA-06512:在“in120032.gen_ids_assessment”，第22行ORA-04088:在执行触发器“in120032.gen_ids_assessment”时出错。ORA-06512:在“in120032.gen_ids_assessment_漏洞”，第12行ORA-04088:在执行触发器“in120032.gen-ids\u assessment”时出错
我不明白的是，在表中插入行很好，而删除行显然不行。
下面是我的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 insert是
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;
/
最佳答案
出现错误的原因是：
1 You delete assessment_flaw.
2 Trigger on assessment_flaw updates assessment.
3 Trigger on assessment queries assessment_flaw <<< mutating table.
不能在触发器中查询要插入、更新和删除的表。
关于database - 删除行时出现Oracle错误:…正在变异，触发器/功能可能看不到，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41163857/