Select row, check condition and update versus update row where condition

I have the following scenario in SQL Server, inside insert trigger on Table2.

Table1

ProductId int(primary key), CurrentStatus, CurrentStatusDate

Table2

NewProductId, NewStatus, NewStatusDate

@NewProductId, @NewStatus, @NewStatusDate FROM inserted

When a new record is added into Table2 I can do:

SELECT @CurrentStatusDate = CurrentStatusDate
FROM Table1
WHERE ProductId = @NewProductId

IF @NewStatusDate >= @CurrentStatusDate
BEGIN
    UPDATE Table1 SET
        CurrentStatus = @NewStatus
        , CurrentStatusDate = @NewStatusDate
    WHERE ProductId = @NewProductId
END

UPDATE Table1 SET
  CurrentStatus = @NewStatus
  , CurrentStatusDate = @NewStatusDate
WHERE ProductId = @NewProductId
AND CurrentStatusDate <= @NewStatusDate

Trigger on Table2 runs tens/hundreds of thousands of times a day. Lets say 90% of the times, NewStatusDate in Table2 is younger than CurrentStatusDate in Table1, so CurrentStatus and CurrentStatusDate have to be updated. Is version 2 absolutely(a lot) better, a little better or there is no difference in terms of performance, risk of deadlocks(we have some experience with triggers unfortunately).

Solution

The right way doesn't involve variables at all, period.

UPDATE t1
   SET CurrentStatus = i.NewStatus,
       CurrentStatusDate = i.NewStatusDate
  FROM inserted AS i
 INNER JOIN dbo.Table1 AS t1
    ON t1.ProductId = i.NewProductId
   AND t1.CurrentStatusDate <= i.NewStatusDate;

If the trigger fires 10s of thousands of times a day, maybe the things that's causing all of that activity should be updated to make the change directly instead of letting the trigger do it? A trigger is a last-resort kind of thing, typically used when you can't change the query/queries to do the right thing.

If there are performance concerns, then you want to make sure the right index exists on Table1 so that an update is unlikely to require a scan to determine the rows to update (leading on ProductId followed by at least CurrentStatusDate). Frequency doesn't matter - you can do a billion point lookups a day, it's the scans that are hard to scale.