Table is mutating on BEFORE DELETE trigger

Question

Basically, i have these 2 tables:

CREATE TABLE Aposta (
    codMovimento number(10)
        references Movimento(codMovimento),
    Primary key(codMovimento),
    data DATE default sysdate not null,
    valor Number(10,2) not null,
    quotaTotal Number(10,2) not null,
    CodTipoAposta Number(1) not null
        references TipoAposta(CodTipoAposta) 
    CodEstadoAposta Number(1)
        references EstadoAposta(CodEstadoAposta)
);

CREATE TABLE LinhaAposta (
    CodMovimento Number(10)
        references Movimento(CodMovimento),
    CodEvento Number(10)
        references Evento(CodEvento),
    Primary key(CodMovimento,CodEvento),
    quota Number(10,2) not null,
    resultado VARCHAR2(1) not null
    check (resultado in ('1','X','2'))
);

I created this BEFORE DELETE trigger to divide the quotaTotal value by the quota value that was being removed. It also needs to update the value of codTipoAposta based on the number of rows of the table linhaaposta. I already tried making it AFTER DELETE but the same error shows up in the script output, and also tried :NEW instead of :OLD.

Trigger:

create or replace TRIGGER update_Quota_Estado2
BEFORE DELETE ON linhaaposta
FOR EACH ROW
DECLARE
updateCount number(3);
BEGIN
    UPDATE Aposta SET quotaTotal= quotaTotal / :OLD.quota WHERE codMovimento = :OLD.codMovimento;
    SELECT COUNT(*) INTO updateCount FROM linhaaposta WHERE codMovimento = :OLD.codMovimento;
    IF (updateCount = '0') THEN
        UPDATE Aposta SET codTipoAposta = 0 WHERE codMovimento = :OLD.codMovimento;
    END IF;
    IF (updateCount >= '1') THEN
        UPDATE Aposta SET codTipoAposta = 1 WHERE codMovimento = :OLD.codMovimento;
    END IF;
END;

I made a similar BEFORE INSERT trigger that works perfectly but does the opposite, multiplies the value inserted on quota and updates it to quotaTotal on the table Aposta.

Here's that trigger:

CREATE OR REPLACE TRIGGER update_Quota_Estado
BEFORE INSERT ON linhaaposta
FOR EACH ROW
DECLARE
    updateCount number(3);
BEGIN
    UPDATE Aposta SET quotaTotal= quotaTotal * :NEW.quota WHERE codMovimento = :NEW.codMovimento;
    SELECT COUNT(*) INTO updateCount FROM linhaaposta WHERE codMovimento = :NEW.codMovimento;
    IF (updateCount = '0') THEN
        UPDATE Aposta SET codTipoAposta = 0 WHERE codMovimento = :NEW.codMovimento;
    END IF;
    IF (updateCount >= '1') THEN
        UPDATE Aposta SET codTipoAposta = 1 WHERE codMovimento = :NEW.codMovimento;
    END IF;
END;

Whenever the 1st trigger is activated, this is the script output:

Error starting at line : 22 in command -
DELETE FROM linhaaposta where codEvento=1
Error report -
ORA-04091: table LUIS.LINHAAPOSTA is mutating, trigger/function may not see it
ORA-06512: at "LUIS.UPDATE_QUOTA_ESTADO2", line 5
ORA-04088: error during execution of trigger 'LUIS.UPDATE_QUOTA_ESTADO2'

From what i tested, it only stops working from the SELECT onwards, tried it without it and the IFs associated and it worked. What's missing / what should i do?

GMB · Accepted Answer · 2019-01-03T00:42:12.743

1

You have hit error ORA-04091.

You can’t query the table that caused a trigger to fire from within the trigger itself. This is by design in Oracle, to protect transaction integrity.

As explained in this other SO post, one solution is to use autonomous transaction. This works by adding a specific pragma in the DECLARE section of your trigger code ; the trigger operations happen in a separate database transaction, that you must commit at the end of the trigger code, like :

CREATE TRIGGER
    ...
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    ...
    COMMIT;
END;

edited Jan 03 '19 at 00:42

answered Jan 02 '19 at 23:56

GMB

216,147
25
84
135

How do you explain the trigger update_Quota_Estado working then? They're pretty much the same and one's working and the other one is not. – lcvalves Jan 03 '19 at 00:02
Hey again, tried PRAGMA AUTONOMOUS_TRANSACTION and it works, the only problem is that the codTipoAposta doesn't change when the updateCount value = 1. – lcvalves Jan 03 '19 at 00:28
@LyZ4RD mm, since this is an autonomous transaction, you need to commit it ; see my updated answer. Also, you should remove the quotes around numbers, like « updateCount >= 1 » instead of « updateCount >= '1' ». – GMB Jan 03 '19 at 00:45
I figured it out, since it is a before delete, the number on IFs(updateCount) should be 2 and 3, not 0 and 1. Thanks for helping, appreciate it. :) – lcvalves Jan 03 '19 at 01:01

Table is mutating on BEFORE DELETE trigger

1 Answers1