Как я могу закодировать триггер IF UPDATING в Oracle Database 10g?

Я кодирую триггер, чтобы гарантировать, что только один тип денег может быть установлен в качестве официального. Мое намерение - закодировать триггер "BEFORE INSERT OR UPDATE". Раздел INSERT работает нормально, но проблема заключается в кодировании раздела UPDATING, потому что, когда я пытаюсь обновить таблицу, я получаю ORA-04091 «таблица мутантов». Есть ли у вас какие-либо идеи?

Таблица (только одна запись может быть установлена ​​как «Y»):

    mon_id  mon_description  mon_official
----------------------------------------------
    E            EUR              N
    D            DOL              N
    P            PES              Y

Курок:

CREATE OR REPLACE TRIGGER mon_oficial_ins_trg
BEFORE
INSERT OR UPDATE
ON monedas
FOR EACH ROW
DECLARE
    v_count  NUMBER(8);
BEGIN
    IF INSERTING THEN

        SELECT COUNT(mon_oficial)
        INTO   v_count
        FROM   monedas
        WHERE  mon_oficial = 'Y';

        IF v_count = 1 THEN
            RAISE_APPLICATION_ERROR(
                -20010, 'Only one record can be set as 'Y'');
        END IF;

    END IF;

    IF UPDATING THEN

        SELECT COUNT(:OLD.mon_oficial)
        INTO   v_count
        FROM   monedas
        WHERE  :OLD.mon_oficial = 'Y';

        IF v_count = 1 AND :NEW.mon_oficial = 'Y' THEN
                RAISE_APPLICATION_ERROR(
                    -20010, 'Only one record can be set as 'Y'');
        END IF;

    END IF;


END mon_oficial_ins_trg;
/
SHOW ERRORS;

oracle triggers plsql dml mutating-table
person Young Al Capone    schedule 30.06.2017    source источник

Ответы (3)


arrow_upward
1
arrow_downward

В вашем коде 2 ошибки

первый

SELECT COUNT(:OLD.mon_oficial)
        INTO   v_count
        FROM   monedas
        WHERE  :OLD.mon_oficial = 'Y';

часть, для получения дополнительной информации об ошибке мутирования вы можете прочитать эту статью

введите здесь описание ссылки

и вторая ошибка, у вас неправильная логика в

IF v_count = 1 AND :NEW.mon_oficial = 'Y' THEN часть, потому что это может быть наша текущая строка

попытайся

    CREATE OR REPLACE TRIGGER mon_oficial_ins_trg
BEFORE
INSERT OR UPDATE
ON monedas
FOR EACH ROW
DECLARE
    v_count  NUMBER(8);
BEGIN
    IF INSERTING THEN

        SELECT COUNT(mon_oficial)
        INTO   v_count
        FROM   monedas
        WHERE  mon_oficial = 'Y';

        IF v_count = 1 THEN
            RAISE_APPLICATION_ERROR(
                -20010, 'Only one record can be set as 'Y'');
        END IF;

    END IF;

    IF UPDATING THEN
     IF :NEW.mon_oficial = 'Y' then 
    for m in (SELECT *
        FROM   monedas
        WHERE  mon_oficial = 'Y'
        and    rownum=1) loop

            IF :NEW.mon_id <> m.mon_id  THEN
                    RAISE_APPLICATION_ERROR(
                        -20010, 'Only one record can be set as 'Y'');
            END IF;
        END IF;
        end loop;
    END IF;


END mon_oficial_ins_trg;
/
SHOW ERRORS;
person Vecchiasignora    schedule 30.06.2017

arrow_upward
0
arrow_downward

Это можно сделать довольно просто с помощью триггера оператора AFTER INSERT OR UPDATE. К обеим операциям применима одна и та же логика.

SELECT COUNT(*) INTO v_count FROM MONEDAS WHERE MON_OFICIAL = 'Y';

ЕСЛИ v_count > 1, ТО RAISE_APPLICATION_ERROR...

Еще одно преимущество этого подхода: он позволяет утверждать

ОБНОВЛЕНИЕ MONEDAS SET MON_OFICIAL = CASE MON_ID WHEN 'A' THEN 'Y' ELSE 'N' END;

работать без проблем, когда триггер на уровне строки может вызвать ошибку, если строка с MON_ID = 'A' будет обновлена ​​до Y до того, как предыдущая официальная валюта будет обновлена ​​​​до N.

person Michael P Stein    schedule 22.07.2019

arrow_upward
0
arrow_downward

Я думаю, что эту проблему лучше решить с помощью ограничения, а не триггера. Я не автор этого ответа, но я думаю, что это уместно здесь. В остальных ответах была ссылка на сообщение в блоге, в котором рекомендуется избегать триггеров, но эта ссылка, похоже, не работает.

Ответ находится здесь: https://stackoverflow.com/a/182427

Вот пример, приведенный в этом ответе @tony-andrews:

create unique index only_one_yes on mytable
(case when col='YES' then 'YES' end);
person s0n1c    schedule 30.04.2020