Default value for modified by column best practice

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

General SQL Server Forums

New to SQL Server Programming

Default value for modified by column best practice

Author

Topic

Racho
Starting Member

13 Posts

Posted - 2009-08-27 : 04:12:13

We have an ICT team that will be working on converting a legacy database to MS SQL Server 2008, so please excuse my ignorance on all things SQL and .NET as I am on the 1st rungs of a steep learning ladder.

The legacy database had a VB like programming language built in so the question below did not arise.

In our .NET application layer we will cater for updating the 'ModifiedBy' data field which will be a numeric Id field from the Internal STAFF Table.

My question is that whenever one of our ICT team jumps into SSMS I would like similiar functionality but here is the issue...

In .NET at startup of the application each user's machine can determine their ID and set a property/constant to that nr by reading the SUSER_ID() value or SUSER_NAME() and look up the ID value from the STAFF Table. If the user then modifies a million records then the property/constant is already set so processing time is fast as no further functions/calculations needed.

I am looking to have the same kind of functionality when any of our ICT staff launch SSMS, i.e. some kind of a batch program that looks up the user, looks up their ID number from the STAFF Table and stores this number in a global type variable

Then when either manually editing or in processing a TSQL command that may update thousands or millions of records the 'ModifiedBy' column can take this global value rather than performing the lookup function on each iteration

Here's hoping I've adequately explained the above issue and that someone can give me best practice solution to the question

Thanks

SQL2008 and Visual Studio 2008 (VB Preferred)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-27 : 09:06:34

You could do it with a trigger, but know that you'll take a performance hit, especially on the very large updates.

Also, you'll have problems if any of your users aren't in the users table


create trigger aTrigger on yourTable for update
as
Declare @userId int
SELECT	 @userId = id FROM users WHERE username = system_user

UPDATE	t
SET	modifiedBy = @userID,
	modifiedDate = getdate()
FROM	yourTable t
JOIN	inserted i

Racho
Starting Member

13 Posts

Posted - 2009-08-27 : 10:07:20

But the need for speed( plus issue of best practice) is what I am after

One of my colleagues, after reading my post, wondered if an appropriate global variable id value could be set up as an environment variable.

Then both TSQL (presumably?) and my .NET solution could both read and use this value when needed?

i.e...

At PC run time have a (or add to an existing) batch file that reads USERNAME from the environment variable does a lookup in the STAFF Table and obtains the numeric ID for the user and writes this value to a new environmnet variable

At run time of .NET application (and also SSMS) reads and use this environment variable to write the value to the CreatedBy and/or ModifiedBy columns

Is this doable?

Is this (or a similiar process that the forum could help me with) approaching best practice and crucially avoid the bottleneck of repeatedly seeking the UsersID from a Table?)

SQL2008 and Visual Studio 2008 (VB Preferred)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-27 : 11:43:37

No such thing as a global variable like that. For your .Net application, you can put whatever you want in a variable and use it with your updates. But for someone who opens up SSMS and starts modifying the data, a trigger is your only recourse (or an auditing application like Lumigent AuditDB).

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-27 : 11:44:29

Best practice, by the way, is to tightly control who has direct access to the database.

Racho
Starting Member

13 Posts

Posted - 2009-08-28 : 04:00:04

Thanks

I think I'll try a new thread and frame my question better

SQL2008 and Visual Studio 2008 (VB Preferred)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources