SQL Server after update trigger

user3927897 picture user3927897 · Aug 29, 2014 · Viewed 206.3k times · Source

I have a problem with this trigger. I would like it to update the requested information only to the row in question (the one I just updated) and not the entire table.

CREATE TRIGGER [dbo].[after_update] 
    ON [dbo].[MYTABLE]
    AFTER UPDATE
    AS 
    BEGIN
          UPDATE MYTABLE 
          SET mytable.CHANGED_ON = GETDATE(),
          CHANGED_BY=USER_NAME(USER_ID())

How do I tell the trigger that this applies only to the row in question?

Answer

Juan picture Juan · Mar 14, 2015

Here is my example after a test

CREATE TRIGGER [dbo].UpdateTasadoresName 
ON [dbo].Tasadores  
FOR  UPDATE
AS 
      UPDATE Tasadores 
      SET NombreCompleto = RTRIM( Tasadores.Nombre + ' ' + isnull(Tasadores.ApellidoPaterno,'') + ' ' + isnull(Tasadores.ApellidoMaterno,'')    )  
      FROM Tasadores 
    INNER JOIN INSERTED i ON Tasadores.id = i.id

The inserted special table will have the information from the updated record.