How to update a field while inserting a row

First, why do you want to store firm_id AND firm_code in the offices table?
(See
When it comes to your insert, if you choose the trigger solution you'll need one insert trigger and one update trigger to make sure firm_id and firm_code always are consistent. (If you remove the firm_code column from the offices table, you don't have that problem!)

Hello JarlH,

Thanks for your reply. Let's just say I need the firm_code and the firm_id. Since I have not done triggers, would it be possible to show how the sql syntax look like? Also, would two triggers work in this situation? Since firm_id in the offices table is NOT NULL, I'm not sure if the insert would even work. Is it possible to retreive the firm_id before the insert, update the firm_id of the inserted row, and insert the row?

Thanks again.

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.
[tt]
create view offices_view (office_id, office_code, firm_id, firm_code, name) as
select office_id,
office_code,
firm_id,
(select firm_code from firms where office.firm_id = firms .firm_id),
name
from office
[/tt]

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.)
[tt]
create table firms (
firm_id integer autoincrement,
firm_code varchar(25) not null unique,
name varchar(100),
primary key (firm_id,firm_code));

create table offices
(office_id integer primary key autoincrement,
office_code varchar(25),
firm_id integer not null,
firm_code varchar(25),
name varchar(100),
foreign key (firm_id ,firm_code) references firms);
[/tt]

The trigger used at insery will look something like:
[tt]
create trigger ins_off before insert on offices
referencing new row as n for each row
begin atomic
set n.firm_code = (select firm_code from firms
where firm_id = n.firm_id);
end
[/tt]

Create a similar one for update as well!