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