Change Date/Time on update - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Change Date/Time on update

Author

Topic

sanj
Starting Member

22 Posts

Posted - 2010-08-26 : 16:25:14

Hi,

I have a column that is set to smalldatetime, I would like update the date/time whenever the record is changed/added, I tried using (getdate()) but this does not change on an update - how could I accomplish this?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-26 : 16:28:20

You can force it with an update trigger that way you guarantee it gets updated even if the programmer forget to send in getdate() on update.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sanj
Starting Member

22 Posts

Posted - 2010-08-26 : 19:00:56

could you please provide me with an example?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-26 : 19:09:44

CREATE TRIGGER [TR_updateColWithDate]
ON [dbo].[yourTable]
FOR UPDATE
AS
BEGIN
UPDATE t
SET ColWithDate = GetDate()
FROM youtTable t
INNER JOIN inserted i -- inserted is the actual name
ON t.id = i.id -- (whatever your unique identifier is)
END

Jim

Everyday I learn something that somebody else already knew

sanj
Starting Member

22 Posts

Posted - 2010-08-27 : 18:55:51

Thanks Jim for your help, I have changed the code provided to suit my DB as:


CREATE TRIGGER [TR_updateColWithDate]
ON [dbo].[tbl_assets]
FOR UPDATE
AS
BEGIN
UPDATE t
SET dateedited = GetDate() 
FROM tbl_assets t
INNER JOIN inserted i -- inserted is the actual name
ON t.id = i.id -- (whatever your unique identifier is)
END

the unique identifier is ID (autonumber) but cannot get this work, am I doing something wrong?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-27 : 19:03:07

Any error messages or what is wrong?

No, you're never too old to Yak'n'Roll if you're too young to die.

sanj
Starting Member

22 Posts

Posted - 2010-08-27 : 19:21:46

The error message reads:

---------------------------
Microsoft SQL Server Management Studio
---------------------------
No row was updated.

The data in row 1 was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).

Correct the errors and retry or press ESC to cancel the change(s).

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources