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