Trigger

[zubby01]

I'm trying to create an after insert trigger.
....
AFTER INSERT AS
BEGIN

update job_function
set job_function.user_initials =
(Select up.user_initials from user_profile upwhere inserted.user_id = up.user_id)

However, it is updating all of the rows - I only want it to update the row I am inserting

 

[fluteplr]

the primary key for your table is not obvious but ise this as an example

update job_function
set job_function.user_initials =
(Select up.user_initials from user_profile upwhere inserted.user_id = up.user_id)
where job_function.primarykey = inserted.primarykey

Have a look at the trigger documentation

inserted and deleted are psuedo tables that are available within triggers.

Inserted has all the new records on a insert and the new data on an update

deleted has all the old records on a delete and the old data on an update

 

[zubby01]

update job_function
set user_initials =
(Select user_profile.user_initials from user_profile where inserted.user_id = user_profile.user_id)
where job_function.user_id = inserted.user_id
and job_function.function_code = inserted.function_code

The column prefix 'inserted' does not match with a table name or alias name used in the query.

What am I doing wrong?

 

[swampBoogie]

update job_function
   set user_initials =
    (Select user_initials 
       from user_profile
      where user_profile.user_id = job_function.user_id )
  from job_function , inserted 
 where job_function.user_id = inserted.user_id
   and job_function.function_code = inserted.function_code

 

[tlbroadbent]

Here is an alternate query.

update job_function
set user_initials = user_profile.user_initials
from job_function
join inserted
On job_function.user_id = inserted.user_id
and job_function.function_code = inserted.function_code
Join user_profile
On inserted.user_id = user_profile.user_id


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm