创建UPDATE触发器,导致删除触发行
我在Oracle Express 11g上。我试图创建此触发器:创建UPDATE触发器,导致删除触发行
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.
其实,我知道我编辑激发触发器在同一个表。但是我想取消更新,或者敲定它,我不在意,然后删除那行。
你能帮我吗?我怎样才能实现我想要做的?
你可以使用一个语句级触发器是这样的:
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;
谢谢....... :) – gd1 2012-04-10 14:11:59
+1。但是,如果表很大,这可能会为表上的每个更新添加一个重要的性能问题......这可能会被适当的基于函数的索引缓解。 – 2012-04-11 02:41:34
可能是语句执行的触发器或PL/SQL函数。该触发器/函数试图修改或查询触发器/函数正在修改的表。请检查http://www.techonthenet.com/oracle/errors/ora04091.php – user1127214 2012-04-10 12:46:33
@ user1127214:是的,这是重点:) – gd1 2012-04-10 12:47:48