SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Change Date/Time on update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanj
Starting Member

22 Posts

Posted - 08/26/2010 :  16:25:14  Show Profile  Reply with Quote
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

USA
36581 Posts

Posted - 08/26/2010 :  16:28:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sanj
Starting Member

22 Posts

Posted - 08/26/2010 :  19:00:56  Show Profile  Reply with Quote
could you please provide me with an example?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/26/2010 :  19:09:44  Show Profile  Reply with Quote
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
Go to Top of Page

sanj
Starting Member

22 Posts

Posted - 08/27/2010 :  18:55:51  Show Profile  Reply with Quote
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?

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 08/27/2010 :  19:03:07  Show Profile  Visit webfred's Homepage  Reply with Quote
Any error messages or what is wrong?


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

sanj
Starting Member

22 Posts

Posted - 08/27/2010 :  19:21:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000