Keep Tracking Lastupdated Datetime In A Table


First, you need to add a Lastupdated datetime column to your table and set the data type as datetime. After you created the column, you can assign a default value to the column with a GETDATE() function, which will insert a dattime when a new record is created. The last piece is to create a AFTER UPDATE trigger for the table.
Each time, when a record is being updated, the lastupdateddatetime will be updated accordingly.
 
Here is the sample code for the update trigger I created for a sample table:
 

CREATE TRIGGER updatedatetimeChanged

ON newTable

AFTER UPDATE

AS

BEGIN

–newTable is the table name with a primary key field id

SET NOCOUNT ON;

update newTable

set lastupdateddatetime=getdate()

FROM newTable n INNER JOIN deleted d ON n.id=d.id

END

GO

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s