SQLTeam.com | Weblogs | Forums

Update Trigger - Insights

Hi Team,

We have an update trigger on a table which fetches a field from that table and updates in another table.   Trigger is written with the assumption that the updated record count as 1 and it uses SQL's Inserted table ID to fetch the field from source table.  Here is the outline of the statements inside the trigger.

declare @EmailAddress Varchar(100) =''
declare @phonumber Varchar(100) =''

Select @EmailAddress = Email,@phonumber = Phone from Table1 WHERE ID in (SELECT id FROM inserted)

UPDATE Table2
SET Field1= @EmailAddress,
Field2 = @phonumber where foreignkey1=ID

But last week mistakenly we ran an Update statement to set a value for one field without where condition for Table1 which updated all the records in Table1; hence the above trigger updated Table2 with the same email and phone; but this email is not the latest record from Table1.

Now we tried to recreate the entire sequence again with the latest backup; to our surprise the same email and phone gets updated in Table2. We inserted all ID from 'Inserted' to a temp table and this ID is not the last record in that; when we ran the Select statement from the trigger it pulls that particular email as the last record always. Would like to know what is happening in the Inserted table? how it is indexed? what happens when we join that with Table1? No obvious reasons from the query execution plan. Anyone please provide your insights on this. Appreciate your help in this regard.

Triggers should always account for multiple rows. I suspect you want something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER TR_IU_Table1
ON dbo.Table1
AFTER INSERT, UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE T2
	SET Field1 = I.Email
		,Field2 = I.Phone
	FROM dbo.Table2 T2
		JOIN inserted I
			ON T2.foreignkey1 = I.ID;
END
GO

Your code snippit does not make a lot of sense:

  1. I have no idea where ID in the update comes from.
  2. You are putting email and phone from the last row of inserted into the variables.

FYI If you are going to load a variable from a table ALWAYS use SET.
eg
SET @EmailAddress = (SELECT email FROM YourTable WHERE id = @id);
SET @phonenumber = (SELECT phone FROM YourTable WHERE id = @id);

Using SET ensures that if multiple rows are returned an exception is thrown.