Solved

adding timestamp to table

Posted on 2013-05-14
6
380 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

839 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