Таблица изменяется по триггеру BEFORE DELETE

По сути, у меня есть эти 2 таблицы:

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

Я создал этот триггер BEFORE DELETE, чтобы разделить значение quotaTotal на значение удаляемой квоты. Также необходимо обновить значение codTipoAposta на основе количества строк таблицы linhaaposta. Я уже пытался сделать это ПОСЛЕ УДАЛЕНИЯ, но в выводе скрипта появляется та же ошибка, а также пробовал: НОВЫЙ вместо: СТАРЫЙ.

Триггер:

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;

Я сделал аналогичный триггер BEFORE INSERT, который отлично работает, но делает наоборот, умножает значение, вставленное в quota, и обновляет его до quotaTotal в таблице Aposta.

Вот этот триггер:

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;

Всякий раз, когда активируется 1-й триггер, это вывод скрипта:

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'

Из того, что я тестировал, он перестает работать только после SELECT, пробовал без него и связанных IF, и это сработало. Чего не хватает/что делать?


sql oracle triggers mutating-table
person LyZ4RD    schedule 02.01.2019    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы столкнулись с ошибкой ORA-04091.

Вы не можете запросить таблицу, вызвавшую срабатывание триггера, изнутри самого триггера. Это предусмотрено в Oracle для защиты целостности транзакций.

Как объясняется в это другое сообщение SO, одним из решений является использование автономного транзакция. Это работает путем добавления определенной прагмы в раздел DECLARE вашего кода триггера; операции триггера происходят в отдельной транзакции базы данных, которую вы должны зафиксировать в конце кода триггера, например:

CREATE TRIGGER
    ...
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    ...
    COMMIT;
END;
person GMB    schedule 02.01.2019
comment
Как вы тогда объясните работу триггера update_Quota_Estado? Они почти одинаковые, один работает, а другой нет. - person LyZ4RD; 03.01.2019
comment
Привет еще раз, попробовал PRAGMA AUTONOMOUS_TRANSACTION, и это работает, единственная проблема в том, что codTipoAposta не изменяется, когда значение updateCount = 1. - person LyZ4RD; 03.01.2019
comment
@ LyZ4RD мм, так как это автономная транзакция, вам необходимо ее зафиксировать; см. мой обновленный ответ. Кроме того, вы должны убрать кавычки вокруг чисел, например «updateCount ›= 1» вместо «updateCount ›= '1'». - person GMB; 03.01.2019
comment
Я понял это, так как это перед удалением, число в IFs (updateCount) должно быть 2 и 3, а не 0 и 1. Спасибо за помощь, ценю это. :) - person LyZ4RD; 03.01.2019