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

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;