How to update a field while inserting a row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to update a field while inserting a row

Author

Topic

faimuj
Starting Member

9 Posts

Posted - 2012-02-16 : 22:52:55

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-16 : 23:03:16

try asking your question at forums.mysql.com

SQLTeam is on Microsoft SQL Server

KH
[spoiler]Time is always against us[/spoiler]

faimuj
Starting Member

9 Posts

Posted - 2012-02-16 : 23:06:10

I'm actually using Sybase, which is quite similar to SQL Server. I wanted to know the trigger syntax for the issue.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-16 : 23:23:17

something like that


create trigger ti_office on offices for insert as
begin
    update o
    set    firm_id  = f.firm_id
    from   inserted i
           inner join offices o on i.office_id = o.office_id
           inner join firms f   on o.firm_code = f.firm_code
end

KH
[spoiler]Time is always against us[/spoiler]

faimuj
Starting Member

9 Posts

Posted - 2012-02-17 : 10:13:28

Hello khtan,

Thanks for your reply. I tried the trigger code and it did not work. I even removed the NOT NULL attribute for firm_id. It still did not 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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-17 : 10:35:09

i am don't know about Sybase LOAD TABLE, does it disable the trigger while loading ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources