Solved

get date of record insertion

Posted on 2006-06-16
3
269 Views
Last Modified: 2012-08-13
Hi,
I have sql server 2005. Clients will connect to a db on this server and insert some records. I want to have a column that will be filled with the date of record insertion.

I don't want to get the client's date (since he may change the date/time on his system), i want to get the server's date and time.

Regards
0
Comment
Question by:bluesand4
3 Comments
 
LVL 1

Accepted Solution

by:
asduth earned 50 total points
Comment Utility
step 1 - add a new coulmn to your table :

             alter table MyTable add InsertDateTime datetime

step 2 - create a trigger on your table which will insert the timestamp


        create trigger TRIG_MYTable_InsertDateTime
        after insert
        as
        update MYTable set InsertdateTime = getdate() where PK in (select PK from inserted)
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
add a new column to the table
 
alter table Yourtable add CreationTime datetime not null default getdate()


0
 

Author Comment

by:bluesand4
Comment Utility
Hi,
I have the following trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER dbo.hi
   ON  dbo.tblContract
   AFTER INSERT
AS
BEGIN
      SET NOCOUNT ON;

      update dbo.tblContract set inserted = getdate()
END
GO

the column is called inserted
but i have now a problem: this called will be updated with the current time for all records..... I want to affect only the record having been now created

 
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

16 Experts available now in Live!

Get 1:1 Help Now