Trigger

I hope this is not too stupid, but I have a trigger that is supposed to update the office field when the user field is changed. BUT, my trigger updates all of the office fields for all of the users when any one of the users are updated.
How do I update only the record that was just updated?





CREATE TRIGGER [Commission1Office] ON dbo.Relo_Referrals
After UPDATE
AS
IF Update (Comm1AgtID)
Update Relo_Referrals
Set Comm1OffcID = Offices.office_id
FROM Users INNER JOIN
Relo_Referrals ON Users.ID = Relo_Referrals.Comm1AgtID LEFT OUTER JOIN
offices ON Users.LOCATION = offices.officeid

Problem with dineshashanka's solution is that it doesn't cover the trigger firing when more than one row is being updated, so follow Frank's suggestion to add the "inserted" pseudo table:

CREATE TRIGGER [Commission1Office] ON dbo.Relo_Referrals
After UPDATE
AS
IF Update (Comm1AgtID)
Update Relo_Referrals
Set Comm1OffcID = Offices.office_id
FROM Relo_Referrals
INNER JOIN inserted ON Relo_Referrals.<identity_column> = inserted.<identity_column>
INNER JOIN Users ON Users.ID = Relo_Referrals.Comm1AgtID
INNER JOIN offices ON Users.LOCATION = offices.officeid

I'm assuming that Relo_Referrals has an identity column, else you'll need to join on all fields of the primary key.

I was just about to post something about the "inserted" temp table when i read this post so thought i should put my comments here.
In answer to the posted question you can refer to the updated trips when using a trigger by a join between the Updated Table and the "inserted" temp table which is a sub set of the data that has been changed
Hence

CREATE TRIGGER [Commission1Office] ON dbo.Relo_Referrals
After UPDATE
AS
IF Update (Comm1AgtID)
Update Relo_Referrals
Set Comm1OffcID = Offices.office_id
FROM Users INNER JOIN
Relo_Referrals ON Users.ID = inserted.Comm1AgtID LEFT OUTER JOIN
offices ON Users.LOCATION = offices.officeid


My comment was going to be I find using the inserted table tends to be impair performance of queries ... in execution plans the majority of the query, when the logic dictates it should use the inserted table, will be taken up with accessing the inserted table.
is there any tweaks or different approaches when using the inserted table?
I look forwad to your comments.

Why use an outer join? Only necessary when you want to update to null in case there is no match. Inner joins are usually 'quicker' than outer joins.

[EDIT:]... and of course UPDATE queries are just plain slower than SELECTs.

Adriaan
If you are referring to the code I posted I simply copied the first authors code and switched in the inserted table. I agree though , the outer join does look a little out of place.

the system in question is a real time warehouse operation
Here is a sample of the trigger

-- verify all conditions for this update are present
SELECT job_no,trp_trip_no FROM inserted
join jobs ON TRP_Job_No = JOB_No
WHERE JOB_Type = 'K'
and TRP_Status = 'I'
and TRP_Cartons > 0

if @@rowcount = 1


if the rowcount is met then i will update a number of other tables....
the subsequent updates don't seem to be the problem however using the inserted table even when the condition is not met takes considerably longer the update which triggered it.. at least on the execution plan that is.
I was wondering whether there is somekind of trigger caching configuration i have failed to set?

Your query doesn't perform a join operation, but it is returning a cartesian product between (a) all rows in inserted, and (b) all rows from your JOBS table that match the criteria in the WHERE statement.

Your query returns (a)*(b) rows, instead of (a) rows.

Please look up the proper syntax for JOIN.

Hint #1: you need to join inserted and JOBS on the key field(s) in order to reduce the number of rows.

Hint #2: at the start of your trigger, before any other statement is executed, you can use @@ROWCOUNT to ascertain the number of rows affected by the event that fired the trigger.

I am not sure i understand you adrian.

the field JOB_no is the PK in the table JOBS
The field trp_job_no is the FK on the table inserted represents.
I will thus return the rows from inserted and jobs which match and where the where clause is matched...

My understanding is that this will not produce a cartesian.
I stand to be corrected.

K

Kramer,

No problem, I wasn't reading close enough ... If I may so bold as to give you another hint - use table names or aliases with each column in the query, it makes it a lot easier to understand. Plus it can actually help improve execution time!

You might try adding the FK table as well, JOINed on inserted, as I'm not sure there are any indexes available when you refer to inserted by itself.

I take your point as regards using an alias.

I too am unsure as to whether inserted uses an index, in fact i am pretty sure it does not.
However i do not know how to include the FK table in the manner you are suggesting without using the Inserted table.
Can you expand on the technique you would use?

Just use regular joins:

SELECT inserted.*
FROM inserted
INNER JOIN FKTable ON inserted.Key = FKTable.Key
INNER JOIN Jobs ON FKTable.Key = Jobs.ForeignKey

I would assume that inserted does 'have' a primary key, so joining it to the underlying table on the key column(s) should not present a problem.

Kramer -- After the join you are checking if @@rowcount = 1 . So what will happen if the join fetches lot of rows. Correct me if I am wrong but I think you may have lot of rows. Seems you want to check if there is only one row that meets the criteria.
Use Top 2 in your join and then see what happens.

Kramer -- I again read your query and in comments you say that "Verify if all the conditions are met " , So i think you may want to use if exists ( select 1 from ..... ) if I am correct.