Início / dba / Perguntas / 322759
Accepted
Hasan Can Saral
Asked: 2023-01-31 02:41:15 +0800 CST

A tabela ORA-04091 está mutando, o gatilho/função pode não vê-lo quando em cascata, funciona de outra forma

  • 772

Tenho as duas tabelas a seguir:

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
);

Preciso atualizar last_updated_atsempre que houver uma alteração/exclusão na tabela entriesor entry_details, então tenho esta trigger:

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;

Isso funciona bem quando há uma exclusão entry_detailsdiretamente. No entanto, recebo o ORA-04091: entries table is mutatingerro quando há exclusão da entriestabela que se propaga para entry_details. Eu tentei:

  1. PRAGMA AUTONOMOUS_TRANSACTIONSque cede aORA-00060: deadlock detected while waiting for resource
  2. Usando uma AFTER DELETEtrigger, que deu novamente, a tabela está com erro de mutação
  3. Não foi possível usar INSTEAD OF DELETEporque não é para tabelas conformeORA-25002: cannot create INSTEAD OF triggers on tables
  4. Eu removi CASCADEe adicionei o seguinte gatilho:

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;

Mas ainda recebo o ORA-04091 table is mutating trigger/function may not seeerro. Como faço para superar isso?

oracle

1 respostas

  1. Best Answer

    Talvez você possa usar um sinalizador que é definido quando uma linha na tabela pai é excluída e desativada após suas exclusões. Você pode tentar o seguinte código:

    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
);

create or replace package trigger_state
as
    cascading BOOLEAN := FALSE;
end;
/


CREATE OR REPLACE TRIGGER entry_before_delete_trigger
    BEFORE DELETE
    ON entries
    FOR EACH ROW
BEGIN
    trigger_state.cascading := TRUE;
END;
/

CREATE OR REPLACE TRIGGER entry_after_delete_trigger
    AFTER DELETE
    ON entries
    FOR EACH ROW
BEGIN
    trigger_state.cascading := FALSE;
END;
/


CREATE OR REPLACE TRIGGER detail_delete_trigger
    BEFORE DELETE
    ON entry_details
    FOR EACH ROW
BEGIN
    IF NOT trigger_state.cascading THEN
        UPDATE entries
        SET last_updated_at = CURRENT_TIMESTAMP
        WHERE id = :OLD.entry_id;
        END IF;
END;
/

    com dados de teste

    insert into entries(id) values(1);
insert into entries(id) values(2);
insert into entries(id) values(3);
insert into entries(id) values(4);
insert into entry_details(id,entry_id) values(1,1);
insert into entry_details(id,entry_id) values(2,1);
insert into entry_details(id,entry_id) values(3,3);
insert into entry_details(id,entry_id) values(4,3);
insert into entry_details(id,entry_id) values(5,3);
insert into entry_details(id,entry_id) values(6,4);
insert into entry_details(id,entry_id) values(7,4);
commit;

    Agora tente deletar

    delete from entry_details where id=6;
commit;

    Isso atualizará a linha pai.

    • 1

relate perguntas