Trigger Only Updates One Row

Recently I was asked to look at a TRIGGER and was told it had stopped working and was only updating a single row instead of all the rows when an UPDATE statement was fired. Almost immediately, without even looking, I could guess what the issue might be.

Very often developers writing a trigger, are tunnel focused on their code at hand. Often their situation only involves a single row. So, they eagerly code away, test and deploy. Then at some point in the future, someone else writes code where multiple rows are UPDATED and the TRIGGER seems to fail. As a BEST PRACTICE, first, I try to avoid using triggers, then once it’s determined this is the only workable solution, I make sure the code will handle multiple rows.

Here is a quick example we can test to show how two different scenarios affect the data. To start with I’m going to create a USERS TEST TABLE and populate some DATA.

CREATE TABLE [dbo].[UsersTest](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LoginName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[UpdateCount] [int] NULL,
	[LastUpdated] [datetime] NULL,
 CONSTRAINT [PK_UsersTest] PRIMARY KEY CLUSTERED 
([ID] ASC) ) ON [PRIMARY]
GO


  INSERT INTO [dbo].[UsersTest] (LoginName, FirstName, LastName, UpdateCount)
  VALUES ('TAdams','Tom','Adams',1),
  ('JBates','Julie','Bates',1),
  ('SSands','Sarah','Sands',1);

Next, I am going to write a trigger that will handle UPDATING the COUNT and DATE columns whenever an UPDATE occurs.

 CREATE TRIGGER trig_UsersTest_Update_BAD_EXAMPLE ON dbo.UsersTest
 AFTER UPDATE
 AS

 DECLARE @UserID int;
	SELECT @UserID = i.ID FROM inserted AS i;
	UPDATE dbo.UsersTest
	SET UpdateCount = (UpdateCount+1),
		LastUpdated = getdate()
	WHERE ID = @UserID;


 GO

Now I’ll run an UPDATE statement to test this.

select * from dbo.UsersTest

 UPDATE dbo.UsersTest
 SET FirstName = FirstName
 WHERE ID > 0;

select * from dbo.UsersTest

Notice how my UPDATE statement updated ALL rows, but the TRIGGER only updated the first row. What gives? Looking back at the TRIGGER code, we set a variable @USERID. That value is only set once and only affects a single row with that ID. Therefore, the TRIGGER, as written, is only capable of updating one single row.

Next, I will DROP the trigger and create a better example.

 CREATE TRIGGER trig_UsersTest_Update_BETTER ON dbo.UsersTest
 AFTER UPDATE
 AS
	if @@rowcount = 0 		
		return	
	set nocount on	
	if exists(select * from inserted)
		UPDATE A
		SET UpdateCount = (A.UpdateCount+1),
			LastUpdated = getdate()
		FROM dbo.UsersTest AS A
		INNER JOIN inserted as I ON A.ID = I.ID
		WHERE I.ID IS NOT NULL;

  GO

In this example, I’ve JOINED the TABLE with the INSERTED data and added some additional error trapping code. When we run the same update statement again, ALL rows are updated this time.

To recap, avoid TRIGGERS when possible. Use a JOIN statement rather than code that would apply to only a single row, when you have to implement a trigger.