How to update a field while inserting a row
How to update a field while inserting a row
(OP)
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.
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.
Talk To Other Members
RE: How to update a field while inserting a row
(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!)
RE: How to update a field while inserting a row
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.
RE: How to update a field while inserting a row
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
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,
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);
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
begin atomic
set n.firm_code = (select firm_code from firms
where firm_id = n.firm_id);
end
Create a similar one for update as well!