trigger to update in Oracle

Question

I've tried in many ways to create a trigger to update a field after this field is updated on other register. Always fail.

When I use FOR EACH ROW I get the mutating table error. And when I try to update directly, I don't know how to reference the new value.

Each person can have more than one address, but only one default. So when the person marks one specific address to be the default, the others must be Zero.

CREATE OR REPLACE TRIGGER trg_aiur_default_address
BEFORE UPDATE ON address FOR EACH ROW
BEGIN
    UPDATE address SET default_address = 0
    WHERE person_id = :NEW.id;
END;
/

ORA-04091: table person is mutating, trigger/function may not see it

CREATE OR REPLACE TRIGGER trg_aiur_default_address
BEFORE UPDATE ON address
BEGIN
    UPDATE address SET default_address = 0
    WHERE person_id = :NEW.id;
END;
/

ORA-04082: NEW or OLD references not allowed in table level triggers

Answer 1

It is just

:new.cdefault := 0;  

(you can't name the column default, it is reserved)

---

As you commented that it won't work as you'll "update the whole table", well - that's nonsense. Have a look.

SQL> create table person as
  2    select 1 id, 200 cdefault from dual union all
  3    select 2 id, 350 cdefault from dual;

Table created.

SQL> create or replace trigger trg_aiur_default_address
  2    before update on person
  3    for each row
  4  begin
  5      :new.cdefault := 0;
  6  end;
  7  /

Trigger created.

SQL> update person set id = 500 where id = 1;

1 row updated.

SQL> select * from person;

        ID   CDEFAULT
---------- ----------
       500          0            --> see? CDEFAULT = 0
         2        350            --> for ID = 2 nothing changed

SQL>

---

After you (finally) made up your mind and told us that there can be multiple addresses for each person, then I'd suggest a compound trigger which "fixes" the mutating table error.

From my point of view, you should have two tables - person (master) and address (detail; contains all addresses for each person; it has a foreign key that points to the person table).

SQL> create table person
  2    (id       number primary key,
  3     name     varchar2(20)
  4    );

Table created.

SQL> create table address
  2    (id         number primary key,
  3     id_pers    number references person (id),
  4     address    varchar2(30),
  5     cb_default number(1) default 0 not null
  6    );

Table created.

SQL> insert into person (id, name)
  2    select 1, 'Littlefoot' from dual union all
  3    select 2, 'Alberto'    from dual;

2 rows created.

SQL> insert into address (id, id_pers, address, cb_default)
  2    select 1, 1, 'London', 1 from dual union all
  3    select 2, 1, 'Paris' , 0 from dual union all
  4    select 3, 1, 'Berlin', 0 from dual union all
  5    select 4, 2, 'Zagreb', 1 from dual;

4 rows created.

SQL>

The compound trigger:

SQL> create or replace trigger trg_bu_dflt_addr
  2    for update on address
  3    compound trigger
  4
  5    type t_rec is table of address%rowtype;
  6    l_tab t_rec;
  7
  8    before statement is
  9      begin
 10        l_tab := t_rec();
 11      end before statement;
 12
 13    before each row is
 14    begin
 15      null;
 16    end before each row;
 17
 18    after each row is
 19      begin
 20        l_tab.extend;
 21        l_tab(l_tab.count()).id := :new.id;
 22        l_tab(l_tab.count()).id_pers := :new.id_pers;
 23        l_tab(l_tab.count()).cb_default := :new.cb_default;
 24      end after each row;
 25
 26    after statement is
 27      begin
 28        for i in 1 .. l_tab.count() loop
 29          if l_tab(i).cb_default = 1 then
 30             update address a set
 31               a.cb_default = 0
 32               where a.id_pers = l_tab(i).id_pers
 33                 and a.id <> l_tab(i).id;
 34          end if;
 35        end loop;
 36      end after statement;
 37  end;
 38  /

Trigger created.

Testing: my current default address is in London; I'll change it to Berlin.

SQL> select * From address order by id;

        ID    ID_PERS ADDRESS                        CB_DEFAULT
---------- ---------- ------------------------------ ----------
         1          1 London                                  1  --> my current default address
         2          1 Paris                                   0
         3          1 Berlin                                  0
         4          2 Zagreb                                  1

SQL> -- setting my default address to Berlin
SQL> update address set cb_default = 1
  2    where id_pers = 1
  3      and id = 3;

1 row updated.

SQL> select * from address order by id;

        ID    ID_PERS ADDRESS                        CB_DEFAULT
---------- ---------- ------------------------------ ----------
         1          1 London                                  0
         2          1 Paris                                   0
         3          1 Berlin                                  1  --> my new default address
         4          2 Zagreb                                  1

SQL>