oracle - 创建导致删除触发行的 UPDATE 触发器

Question

我使用的是 Oracle Express 11g。我正在尝试创建此触发器:

CREATE TRIGGER remove_useless_surveys 
     BEFORE UPDATE OF agent_id, agency_id, province_id ON surveys 
     FOR EACH ROW WHEN (
     new.agent_id IS NULL AND 
     new.agency_id IS NULL AND
     new.province_id IS NULL)
DELETE FROM surveys WHERE survey_code = :new.survey_code

agent_id、agency_id 和 Province_id 是外键，带有 ON DELETE SET NULL 子句。

当所有三个外键都设置为 NULL 时，我需要在调查表中删除一行(因为引用的行被删除)。

触发器编译没有问题，但是当条件触发时，我收到此错误:

SQL error: ORA-04091: table REALESTATE.SURVEYS is mutating,
trigger/function may not see it ORA-06512: at
"REALESTATE.REMOVE_USELESS_SURVEYS", line 1 ORA-04088: error during
execution of trigger 'REALESTATE.REMOVE_USELESS_SURVEYS'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

实际上，我知道我正在编辑触发触发器的同一个表。但是我想取消更新，或者敲定，我不管，然后 删除该行 .

你能帮助我吗？我怎样才能实现我想要做的事情？

Answer 1

您可以使用这样的语句级触发器:

CREATE TRIGGER remove_useless_surveys 
AFTER UPDATE OF agent_id, agency_id, province_id ON surveys 
BEGIN
  DELETE FROM surveys 
  WHERE  agent_id IS NULL 
  AND    agency_id IS NULL
  AND    province_id IS NULL;
END;