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.

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

 

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.

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!