Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

adding timestamp to table

Posted on 2013-05-14
6
Medium Priority
?
395 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 800 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1200 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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