January 14, 2002 at 9:25 am
If i have a table A with X fields and i have an identical table B with the same fields, i wish to insert the record that's being updated into table B using a trigger how would i do this. e.g. Tables A and B have 2 fields empname and empnumber. Table A has record John and 101. If i change empnumber to 102, i wish table B be updated with record John and 101. How would i do this using a trigger?? please help.
January 14, 2002 at 1:08 pm
No magic too it. First figure out how to do it plain SQL from Query Analyzer. Basically something like this:
update b set b.col1=a.col1, bcol2, a.col2 from table1 b inner join table2 a on a.primarykey=b.primary where rowid=1
Once you get it to work, change table2 to "inserted" which is the logical table visible during trigger execution. Something like this:
create trigger u_tablea on tablea for update as
update b set b.col1=a.col1, bcol2, a.col2 from table1 b inner join in inserted a on a.primarykey=b.primary
Andy
January 14, 2002 at 2:44 pm
Yes, create a trigger that fires on INSERT. As long as the table doesn't have any text, ntext, or image columns, you can use the inserted temporary table to find out what's being put into a particular table.
If you have to handle the text columns, with SQL 2K you can use the INSTEAD OF trigger (but make sure that trigger carries out the INSERT to the original table because it's interrupting the normal INSERT). SQL Server 7 doesn't have the INSTEAD OF trigger and you'd have to look at handling this manually.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 16, 2002 at 8:37 am
hey Andy, could you please write the SQL statement based on my example. I did try yours, but i am getting errors. Please help me out.
January 18, 2002 at 9:02 am
Thanks guys for your help. I have figured it out. The trigger works like a charm. Thanks to Andy for giving me the proper clues.
January 18, 2002 at 9:19 am
Also, there is a gotcha if you are using auto identity on the originating table and expect to have that value available after the trigger has completed it's work. You will need to capture the @@identity value before the trigger is called in a temp table with one column one row. At the end of the trigger do a select on that temp table and it will return that value of @@identity. This is particularly important if you are inserting to another table within the trigger.
Jody
January 18, 2002 at 4:35 pm
In SQL2K scope_identity solves the @@identity problem.
Andy
January 21, 2002 at 12:29 pm
you can just insert into table B using insert TableB select * from inserted which will capture multi-row inserts. Keep in mind that TableB should not have an identity.
Steve Jones
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply