×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to update a field while inserting a row

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.

RE: 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 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

(OP)
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.

RE: How to update a field while inserting a row

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close