Skip to content

Instantly share code, notes, and snippets.

@bdcravens
Created December 19, 2012 21:32
Show Gist options
  • Save bdcravens/4340724 to your computer and use it in GitHub Desktop.
Save bdcravens/4340724 to your computer and use it in GitHub Desktop.
SQL Server - add createdAt and updatedAt columns, automatically updates
ALTER TABLE myTable
add createdAt datetime
CONSTRAINT DF_myTable_createdat DEFAULT GETDATE()
ALTER TABLE myTable
add updatedAt datetime
CONSTRAINT DF_myTable_updatedAt DEFAULT GETDATE()
go
create trigger trg_myTable_update on myTable for update as
begin
update myTable
set updatedAt = getDate()
from myTable inner join deleted d
on myTable.id=d.id
end
go
@bitterloa
Copy link

excellent code. thank you. for others who want to use this and may not know what the deleted table is referred to in this code--it's a table that SQL Server uses during updates and deletes to hold the records being changed.

See link below or search google to understand why the from myTable inner join deleted d on myTable.id=d.id is necessary:

https://www.mssqltips.com/sqlservertip/2342/understanding-sql-server-inserted-and-deleted-tables-for-dml-triggers/

@amshaik-ic
Copy link

is there any other way to perform auto update timestamp in SQL server instead of triggering ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment