How to update a field while inserting a row

[faimuj]

Hello,

I wanted to know what would be the best way to update a field of a row while I am inserting. Here are the tables and fields (for simplicity I am only including relevant fields):

• firms
⁃ firm_id (integer, autoincrement, primary key)
⁃ firm_code (varchar(25), internal code)
⁃ name (varchar(100), firm name)
• offices
⁃ office_id (integer, autoincrement, primary key)
⁃ office_code (varchar(25), internal code)
⁃ firm_id (integer, foreign key to firms table, NOT NULL)
⁃ firm_code (varchar(25), internal code)
⁃ name (varchar(100), office name)

The firms data has already been loaded. What I want to do is as I load the data into the offices table, via LOAD TABLE command, I want to check the firm_code in the offices data against the firm_code in the firms table and update the firm_id while I perform the insert into the offices table.

I was thinking about writing a trigger to do that; however, since I have never written a trigger, I was not sure how to go about doing that via a trigger. Also, if there are better ways to achieve this, please let me know that as well.

Thank you in advance.

 

[JarlH]

First, why do you want to store firm_id AND firm_code in the offices table?
(See

http://en.wikipedia.org/wiki/Data_redundancy)

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

 

[faimuj]

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.

 

[JarlH]

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!