Update Time in field when record changes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Time in field when record changes

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-03-10 : 09:38:54

How can I update a field with the server time, when that record in a table is updated.

So for example: I have field called Update_Time. When any changes are made to that record, how can I update the Update_Time field with the time of the server as at the time of update.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 09:49:38

this can be either perform by the application or stored procedure when update the record, it also update the column.

Or alternatively use an UPDATE trigger to do it.

KH
[spoiler]Time is always against us[/spoiler]

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-03-10 : 09:53:31

I don't want to use the application as this would take the users PC time which could be anything, unless there is something I am missing!

I could use a Trigger or SP but I dont know how I would identify which record in the table has been updated. What would the SQL be for that?

bals
Starting Member

4 Posts

Posted - 2011-03-10 : 10:23:42

CREATE TABLE tst ( Roll_no INT ,Update_Time DATETIME DEFAULT GETDATE() )
INSERT TST (Roll_no) VALUES (001)
SELECT * FROM TST
G0
CREATE TRIGGER UpdateModifiedDate
ON tst AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE tst
SET Update_Time = GETDATE()
WHERE Roll_no in (SELECT Roll_no FROM INSERTED);
GO

UPDATE TST
SET Roll_no = 005
WHERE Roll_no =001
SELECT * FROM TST

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-03-10 : 10:44:54

Nice one, works a treat.

Many thanks for that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources