Para modificar la fecha de modificación de una tupla automáticamente al actualizar algún campo, seguimos estos pasos:

Añadimos un campo fecha de modificado:

ALTER TABLE dbo.miTabla ADD FechaModificado datetime;

Añadimos un trigger, para que cuando se modifique alguna tupla, nos actualize el campo FechaModificado con la fecha actual del sistema:

CREATE TRIGGER dbo.miTablaTrigger ON dbo.miTabla
AFTER INSERT, UPDATE 
AS
  UPDATE f SET FechaModificado=GETDATE() 
  FROM 
  dbo.miTabla AS f 
  INNER JOIN inserted 
  AS i 
  ON f.id = i.id;

Probamos:

UPDATE dbo.miTabla SET [campo] = '21' WHERE [id] = '7'

Revisamos los cambios:

SELECT TOP 10 * FROM dbo.miTabla