尝试从 assessment_flaw 删除行会导致以下错误：
ORA-04091：表IN120032.ASSESSMENT_FLAW正在变异，触发器/函数可能看不到它ORA-06512：在“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'));
在执行时插入是
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 回答
你得到错误是因为：
您无法在触发器中查询要插入，更新，删除的表 .