howto: automatically store datetime + username

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

howto: automatically store datetime + username

Author

Topic

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2007-11-11 : 05:33:30

Hello, I am new to this forum, and I have great hopes you may help me a lot!
Here is my first question.

I need to track the date-time and the Windows user-name of each record in my database (when updated). I have already several front-end applications and will create a few more as well as many new screens, and I want to make my programmer-life easier.
How could I proceed?

Ideally, I would like to find a solution where I have no need for any line of code for future screens or new tables. When creating new tables, I would hope that creating the two necessary fields would be enough to have them filled automatically.

Do you think it would be possible to get things as easy as that?
Maybe by some magic T-SQL code (I am familiar not familiar with T-SQL)?
Maybe by defining some field type associated with some code(???) ?

Or would there be other ways to go and still be confortable?

Thanks for your suggestions.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-11 : 06:47:48

First of all, you can get the current datetime using getdate() and user name using system_user.

If you are using stored procedure to insert and update records into your table at all times, you can code it in your stored procedure

-- insert
insert into table1( . . . [datecol], [usercol])
select . .  . getdate(), system_user

-- update
update t
set    . . . ,
       [datecol]  = getdate(),
       [userocl]  = system_user
from   table1 t

You can also do it in your table definition during creation. This will only update during record insertion

create table1
(
    . . .
    [datecol] datetime     default getdate(),
    [usercol] varchar(100) default system_user,
    . . . 
)

With the default value, you may omit the 2 column in your insert statement. If you did not omit this 2 columns in your insert statement, you need to pass in the correct value and not NULL value.

But that does not take care of the update statement. If you don't have a stored procedure to perform the update to the table, you can use an update trigger to do the job

update u
set    [datecol] = getdate(),
       [usercol] = system_user
from   inserted i inner join table1 u
       on i.pkcol = u.pkcol

KH
[spoiler]Time is always against us[/spoiler]

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-11 : 09:33:53

Just wanted to add. system_user would help you only if records are modified *directly* (Winforms App connects to SQL server), not thru middle-tier or IIS.

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2007-11-11 : 15:25:46

Thanks a lot.

I am mainly using access mdb project witht the tables being linked.
What would be your suggestion in this case?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources