Trigger that updates just the inserted row

Question

I'm trying to create a simple trigger using TSQL (or SQL Server 2008). The problem is: my current trigger is updating the entire table. This was fine for a while, but now the table has more than 20k rows. So I want a trigger that only updates the rows that are being inserted.

Here's my current simple trigger:

CREATE TRIGGER trig_MyPplUpdate
ON [Persons]
FOR INSERT
AS
Begin
 Update Persons
    set MyFile = NULL
    where Len(MyFile) < 60
End

I think I'll have to use either the "inserted" table or the row_number function ordered by the primary key. Any ideas?

Answer 1

If it is necessary to use a trigger here at all I would use an INSTEAD OF trigger to adjust the values pre-insert and avoid the need to JOIN back onto the base table and Update them afterwards.

CREATE TRIGGER trig_MyPplUpdate
ON [Persons]
INSTEAD OF INSERT
AS
  BEGIN
      INSERT INTO Persons
      SELECT foo,
             bar,
             CASE
               WHEN Len(MyFile) >= 60 THEN MyFile
             END
      FROM   Inserted
  END