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.