ORACLE SQL-在使用cascade set null的父表上删除触发器后，突变错误解决方法

I am trying to update columns in my child table service after delete on parent table cars using function choose_ideal_car which uses some selects over cars and service tables.

我正在尝试使用函数choose_ideal_car在父表汽车上删除之后更新我的子表服务中的列,该函数使用一些选择而不是汽车和服务表。

Here is part of my sql script:

这是我的sql脚本的一部分:

CREATE TABLE cars(car_id INT CONSTRAINT pk_id_cars);
CREATE TABLE service(
service_id INT CONSTRAINT pk_id_service PRIMARY KEY,
car_id INT CONSTRAINT fk_id_car REFERENCES cars(car_id) ON DELETE SET NULL,
period VARCHAR2(5) CONSTRAINT check_period CHECK period IN ('even','odd','every'),
period VARCHAR2(3) CONSTRAINT check_day CHECK day IN ('mon','tue','wed','thu','fri')
);
CREATE OR REPLACE TRIGGER service_set_car_id AFTER DELETE ON cars
DECLARE
CURSOR touched_rows is select * from service where car_id = null;
touched_row service%ROWTYPE;
BEGIN
OPEN touched_rows;
  LOOP
  FETCH touched_rows INTO touched_row;
  EXIT WHEN touched_rows%NOTFOUND;
  UPDATE service SET car_id = choose_ideal_car(touched_row.period,touched_row.day) WHERE service_id = touched_row.service_id;
  END LOOP;
CLOSE touched_rows;
end;
/

For some reason my trigger is never fired.

由于某种原因,我的触发器从未被触发

I also tried creating triggers like this:

我也试过创建这样的触发器:

CREATE OR REPLACE TRIGGER service_set_car_id AFTER DELETE FROM cars FOR EACH ROW
CURSOR touched_rows is select * from service where car_id = :old.car_id;
...

Which is fired but throws 'mutating error' because function choose_ideal_car uses selects from both tables. Maybe the solution of this is to create duplicate of my cars table and select from it in my choose_ideal_car function instead of selecting from the cars table on which is my trigger defined, but that does not sounds good to me.

由于函数choose_ideal_car使用两个表中的选择,因此被触发但抛出'变异错误'。也许这个解决方案是创建我的汽车表的副本并在我的choose_ideal_car函数中从中选择,而不是从我的触发器定义的汽车表中选择,但这对我来说听起来不太好。

While I am writing this post I realized that even if my first trigger is fired it would not work correctly and throw the same 'mutating error'.

当我写这篇文章时,我意识到即使我的第一个触发器被触发它也无法正常工作并抛出相同的“变异错误”。

So in the end I have two questions:

所以最后我有两个问题:

1) Why is the first trigger never fired?

1)为什么第一个触发器永远不会被触发?

2) How to fix this mutating error and get all working correctly?

2)如何解决这个变异错误并使所有工作正常?

1 个解决方案

#1

where car_id = null returns no rows, use where car_id is null instead

其中car_id = null不返回任何行,请使用car_id为null的位置