Sql server on update set current timestamp

And if you really need a timestamp - then make a trigger on insert and update that updates the column with the current timestmap.

CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE dbo.YourTable
  SET last_changed = GETDATE()
  FROM Inserted i

To update a single row (which has been edited or inserted) you should use

CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE f set LastUpdate=GETDATE() 
  FROM 
  dbo.[YourTable] AS f 
  INNER JOIN inserted 
  AS i 
  ON f.rowID = i.rowID;

These should be all you need. GETUTCDATE() if you want it in UTC (which I prefer)

SQL Server absolutely knows the rows it processes

update myTable set last_time =CURRENT_TIMESTAMP ; worked, but it updated all the rows instead of the current.

Yeah, guess what - because that is exactly what you tell SQL Server: Update all rows in the table.

Doesn't Sql Server know which is the actual record it is processing?

Sets have no current row ;) That is where the problem starts.

The only way to do that exactly as you want is up in my answer on the beginning: a timestamp. Due to the misconceptions, though, I add an advice: get a book about SQL basics.

Tags:

Sql Server