Solved

adding timestamp to table

Posted on 2013-05-14
6
370 Views
Last Modified: 2013-05-14
Hi guys
I have a table customer with customer_name
And customer_I'd columns.
I want to add a new column called
Timestamp_ entry and when ever a row is inserted into the table it should insert the current timestamp in this column.
Do I need a trigger for this?
What other ways are possible without a trigger in SQL server if any?

Any help appreciated
 Thanks
0
Comment
Question by:royjayd
6 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39164710
Are you wanting to actually insert the current day and time or are you talking about the old SQL Server 2005 timestamp that didn't actually insert a date/time and is now rowversion?
0
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 200 total points
ID: 39164711
Yeah create a new column and point a trigger to update after insert.

ALTER TABLE dbo.YourTable
ADD COLUMN Timestamp_entry  DATETIME

Open in new window


CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE dbo.YourTable
  SET Timestamp_entry = GETDATE()
  FROM Inserted i
  WHERE dbo.YourTable.customer_Id = i.customer_Id 

Open in new window

0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39164737
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 300 total points
ID: 39164823
If you only need that time stamp when inserting the record, then there is no need for a trigger.

Add that column, using GETDATE() as the default value:

ALTER TABLE customers
ADD timestamp_entry datetime DEFAULT GETDATE()

Open in new window


Just make sure that when performing inserts to that table, you omit the timestamp_entry column.

Note that after running the ALTER TABLE statement above, any pre-existing rows in that table will have NULL for the timestamp_entry column.
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39164913
ha ha matthewspatrick got it nailed.....

i thought you wanted a trigger... but his solution is by far the easiest
0
 

Author Comment

by:royjayd
ID: 39165737
great ..thanks
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now