我有以下两个表:
CREATE TABLE entries
(
id NUMBER(20, 0) NOT NULL PRIMARY KEY,
first_name VARCHAR2(512) DEFAULT NULL NULL,
last_name VARCHAR2(512) DEFAULT NULL NULL,
last_updated_at TIMESTAMP DEFAULT NULL NULL
);
CREATE TABLE entry_details
(
id NUMBER(20, 0) NOT NULL PRIMARY KEY,
entry_id NUMBER(20, 0) NOT NULL,
value VARCHAR2(512) DEFAULT NULL NULL,
CONSTRAINT fk_details_entry_id FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
);
只要or表last_updated_at
中有更改/删除,我就需要更新,所以我有这个触发器:entries
entry_details
CREATE OR REPLACE TRIGGER detail_delete_trigger
BEFORE DELETE
ON entry_details
FOR EACH ROW
BEGIN
UPDATE entries
SET last_updated_at = CURRENT_TIMESTAMP
WHERE id = :OLD.entry_id;
END;
entry_details
当直接从中删除时,这很好用。但是,
ORA-04091: entries table is mutating
当 delete from entries
table 级联到entry_details
. 我努力了:
PRAGMA AUTONOMOUS_TRANSACTIONS
屈服于ORA-00060: deadlock detected while waiting for resource
- 使用
AFTER DELETE
再次给出的触发器,表正在发生变异错误 - 无法使用
INSTEAD OF DELETE
,因为它不适用于表格ORA-25002: cannot create INSTEAD OF triggers on tables
- 我删除
CASCADE
并添加了以下触发器:
ALTER TABLE entry_details DROP CONSTRAINT fk_details_entry_id;
ALTER TABLE entry_detailsADD CONSTRAINT fk_details_entry_idFOREIGN KEY (entry_id) REFERENCES entries(id);
CREATE OR REPLACE TRIGGER aml_entries_delete_trigger
BEFORE DELETE -- or AFTER DELETE, same result
ON entries
FOR EACH ROW
BEGIN
DELETE FROM entry_details WHERE entry_id = :OLD.id;
END;
但我仍然得到ORA-04091 table is mutating trigger/function may not see
错误。我该如何克服这个问题?
也许您可以使用在删除父表中的行并在删除后取消设置时设置的标志。您可以尝试以下代码:
有测试数据
现在尝试删除
这将更新父行。