Solved

adding timestamp to table

Posted on 2013-05-14
6
374 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Do I need CLUSTERED here? 13 45
SQL SERVER 2008 R2 Could not obtain information about Windows NT group/user 5 36
SQL view 2 27
SQL Function NOT ROUND 9 10
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore 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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

777 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