Trigger that updates just the inserted row

问题:

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?

回答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  

回答2:

You need to join the Inserted pseudo table in your UPDATE statement. Always be aware that SQL Server fires the trigger once per statement and that statement can very well modify/insert multiple rows at once - so your Inserted table quite likely will contain more than one row - just need to keep that in mind when you write your trigger.

Try something like this:

CREATE TRIGGER trig_MyPplUpdate
ON [Persons]
FOR INSERT
AS
    UPDATE dbo.Persons
    SET MyFile = NULL
    WHERE Len(MyFile) < 60
    AND PersonID IN (SELECT DISTINCT PersonID FROM Inserted)

or use whatever unique column (your primary key) you have to get exactly those rows that have been inserted.