CODEWITHSUNDEEP
t-sqlsql-server-2012
user1176058
user1176058

Reputation: 676

update the value of a column every time an update is made to the row

I am using SQL Server 2012. I need to track all the rows that were updates (as part of any action) made on a table.

I thought to add a new column (Column Name: "LastUpdated" of type Datetime.) to the table that I want to track. I would like to update the LastUpdated value every time an update is made to that row. Is there a specific way to acheive this task?

Upvotes: 0

Views: 285

Answers (3)

tgolisch
tgolisch

Reputation: 6734

You could create a trigger. Like this: 

CREATE TRIGGER dbo.Table1_Updated
ON dbo.Table1
FOR UPDATE /* Fire this trigger when a row is UPDATEd */
AS BEGIN
   UPDATE dbo.Table1 SET dbo.Table1.LastUpdated = GETDATE()
   FROM INSERTED
   WHERE inserted.id=Table1.id
END

However, this trigger will not store WHAT was updated. Also keep in mind that if two people update it, you will only have the date of the most recent update (not all updates).

To keep a history on all changes, you might want to create an audit table (nearly identical structure to your existing table) and use a trigger to copy the pre-update data into the audit table. This SO article talks about a solid approach: Using table auditing in order to have "snapshots" of table

Upvotes: 2

Kevin Kunderman
Kevin Kunderman

Reputation: 2134

You would want to look into creating a trigger to fire on update

Create TRIGGER Trigger_Name ON Table_Name
FOR update
AS
BEGIN

// update lastupdated column of updated record with current date and or time

END

Alliteratively you could just pass the new value for lastupdated when updating the other fields

Upvotes: 1

Taryn
Taryn

Reputation: 247720

We have implemented a system similar to the following:

  • Add a new column to your table LastUpdatedDate with a datatype of datetime
  • If needed, add another column with LastUpdatedBy with a datatype of varchar(100) or whatever length you need

This will store both the date/time and who performed the update.

Then when you update the row in the table, you will include those two columns in your update statement similar to this:

update yourtable
set yourCol = 'newValue',
   LastUpdatedDate = getdate(),
   LastUpdatedBy = 'yourUserIdentifier'

Upvotes: 0

Related Questions