CODEWITHSUNDEEP
sql-server
starhu
starhu

Reputation: 401

SQL Server : on update set current timestamp

I need a timestamp field which updates every time the user modifies the record.

So far I used MySql in which I can even use this in the field creation:

ALTER TABLE myTable 
    ADD `last_time` timestamp NOT NULL 
        DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP

I couldn't find this possibility in SQL Server.

Then I tried writing a trigger - in a MySql trigger this is simple:

SET new.last_time = CURRENT_TIMESTAMP();

SQL Server doesn't seem to know neither new, nor old syntax, it gave me error on compilation.

This:

UPDATE myTable 
SET last_time = CURRENT_TIMESTAMP;

worked, but it updated all the rows instead of the current.

Isn't there a way the tell SQL Server to update the current record? Should I use UPDATE .... WHERE myid = something ?

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

Upvotes: 12

Views: 120989

Answers (2)

TomTom
TomTom

Reputation: 62093

Make a trigger on insert and update that updates the column with the current timestamp.

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

SQL Server 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.

Upvotes: 27

Sargis Koshkaryan
Sargis Koshkaryan

Reputation: 1030

Use rowversion datatype. Rowversion is generally used as a mechanism for version-stamping table rows in MS-SQL server. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type. For more information please read about rowversion in msdn (http://technet.microsoft.com/en-us/library/ms182776.aspx)

Upvotes: -3

Related Questions