Automatic Date/Time Field - 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

Automatic Date/Time Field

Author

Topic

SQLFOX
Starting Member

45 Posts

Posted - 2011-09-13 : 08:05:19

Hi Guys,

How do I add a column that will 'automatically' enter the time and date that the record was updated?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:08:02

[code]ALTER TABLE <yourtable> ADD <yourcolumnname> datetime NOT NULL DEFAULT (GETDATE())[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLFOX
Starting Member

45 Posts

Posted - 2011-09-13 : 08:09:28

Thanks for replying.

Will this also give me the exact time the record was updated?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-13 : 08:09:29

only for new records or even when you update a record ?

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

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 08:13:47

You need an UPDATE trigger to set the datetime when the record is updated. We also have a DEFAULT (as Visakh has shown) to set the initial value when the record is first INSERTED.

However ... you may not want to do it with a trigger.

For example, if you copy records from Server-A to Server-B they will all be marked as having been updated "now". When we do this we want to retain the date that the last update was made on the original server (the act of copying the data is not regarded as a substantive change).

We use triggers to record an Audit Trail of changes to records, so that logs when changes were made (including copying from Server-A into Server-B), and we thus rely on the CRUD Sprocs etc that save records to set the UpdateDate Column - to the value returned by GetDate()

SQLFOX
Starting Member

45 Posts

Posted - 2011-09-13 : 08:19:39

It works if I create a new record but not if I update a record.

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 08:23:00

See my earlier reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:27:47

for that you need a trigger as Kirsten suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:29:37

and it should be like


CREATE TRIGGER GETAuditInfo
ON YourTable
FOR UPDATE
AS
BEGIN
IF NOT (UPDATE(datecolumn))
UPDATE t
SET t.datecol=GETDATE()
FROM Table t
INNER JOIN INSERTED i 
ON i.PK = t.PK
END

PK is your primary key

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLFOX
Starting Member

45 Posts

Posted - 2011-09-13 : 08:35:01

How do I create a Trigger for this? - I have the following columns in a table called Customers:-

CustomerID, Firstname, Lastname, Address1, Address2, City, TimeUpdated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:38:15

i've given you the stub already. just replace your correct column and table names in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLFOX
Starting Member

45 Posts

Posted - 2011-09-13 : 08:45:19

It didn't work for me. Is there anything wrong with the code below? Also, would you mind explaining what each step in the Trigger does?

Thanks.

CREATE TRIGGER GETAuditInfo
ON Customers
FOR UPDATE
AS
BEGIN
IF NOT (UPDATE(TimeUpdated))
UPDATE t
SET t.TimeUpdated=GETDATE()
FROM Table t
INNER JOIN INSERTED i
ON i.PK = t.PK
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:50:19

can you show your update statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 08:52:55

"ON i.PK = t.PK"

Is that really the name of your Primary Key column? - as Visakh said: "PK is your primary key"

"It didn't work for me"

It would help if you said WHAT didn't work. I assumed that you meant that you updated a record and the Update Date column didn't change - I expect Visakh thought the same from his question.

But more likely you probably got a syntax error when you tried to create the trigger?

SQLFOX
Starting Member

45 Posts

Posted - 2011-09-13 : 08:53:49

Made a slight change to the code and it worked! Thanks for your help. Would you mind explaining how it works? (I'm fairly new to SQL)...

CREATE TRIGGER GETAuditInfo
ON Customers
FOR UPDATE
AS
BEGIN
IF NOT (UPDATE(TimeUpdated))
UPDATE Customers
SET TimeUpdated=GETDATE()
FROM Customers t
INNER JOIN INSERTED i
ON i.CustomerID = t.CustomerID
END

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 09:00:14

"Would you mind explaining how it works?"

http://msdn.microsoft.com/en-us/library/ms189799.aspx

SQLFOX
Starting Member

45 Posts

Posted - 2011-09-13 : 09:08:07

Thanks for the info!! Just wondering why I need to join the Customers table to the Inserted for it to work?

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 10:10:53

Yes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-13 : 10:14:24

Of-course. You need to update the Customer table right ?

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

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 10:15:13

... and just the customers that were updated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 10:32:02

quote:
Originally posted by SQLFOX

Thanks for the info!! Just wondering why I need to join the Customers table to the Inserted for it to work?

inserted is magic table that contains only info on updated records inside update trigger. so joining to it will ensure you're updating date only for those records that was updated recently.
The link Kristen gave should give more elaborate info on magic tables inserted and deleted for various DML operations

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources