How Can I code a "IF UPDATING" trigger in Oracle Database 10g?

Question

I'm coding a Trigger to ensure only one type of money can be set as official. My intention is code a "BEFORE INSERT OR UPDATE" trigger. The INSERT section works fine but the problem is coding the UPDATING section because when I try to update the table I recieve ORA-04091 "mutanting table". Do you have any idea?

Table (Only one record can be set as 'Y'):

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

Trigger:

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;

Answer 1

I think this problem would better be solved with a constraint, instead of a trigger. I am not the author of this answer, but I think it's relevant here. In the rest of the answers there was a link to a blog post that recommends avoiding triggers, but that link doesn't seem to work.

The answer is found here: https://stackoverflow.com/a/182427

Here's the example provided in that answer by @tony-andrews:

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

Answer 2

This can be done quite simply with an AFTER INSERT OR UPDATE statement trigger. The same logic is applicable to both operations.

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

IF v_count > 1 THEN RAISE_APPLICATION_ERROR...

Another advantage of this approach: it allows the statement

UPDATE MONEDAS SET MON_OFICIAL = CASE MON_ID WHEN 'A' THEN 'Y' ELSE 'N' END;

to work without a problem when the row-level trigger might raise an error if the row with MON_ID = 'A' is updated to Y before the previous official currency is updated to N.

Answer 3

In your code there are 2 mistake

first

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

part, for more information about mutanting error you can read this article

enter link description here

and second mistake, you have a incorrect logic in

IF v_count = 1 AND :NEW.mon_oficial = 'Y' THEN part because it can be our current row

try it

    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;