Solved

adding timestamp to table

Posted on 2013-05-14
6
383 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

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 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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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