17 Feb 12 10:39
Have you considered a view, to get both the firm_code and the firm_id? I mean skip the firm_code in the office table, instead create a view that also includes the firm_code column.
create view offices_view (office_id, office_code, firm_id, firm_code, name) as
(select firm_code from firms where office.firm_id = firms .firm_id),
Here you have both columns!!!
Alternatively, if you include both firm_id and firm_code in the firm table's primary key, then the offices' foreign key will ensure consistent data. (Also make firmfirm_code not null and unique.)
create table firms (
firm_id integer autoincrement,
firm_code varchar(25) not null unique,
primary key (firm_id,firm_code));
create table offices
(office_id integer primary key autoincrement,
firm_id integer not null,
foreign key (firm_id ,firm_code) references firms);
The trigger used at insery will look something like:
create trigger ins_off before insert on offices
referencing new row as n for each row
set n.firm_code = (select firm_code from firms
where firm_id = n.firm_id);
Create a similar one for update as well!