SQLTeam.com | Weblogs | Forums

Store userID who created the record

Have 2 tables: Processes and Users
I would like to have a column on the Processes table that stores the user who created the record, so that he can filter the records when needed for editing and completing.
Do I need to create an audit table? Can I trigger the filling of the column right on the Processes table?
Thanks in advance.

You could add a user column with default of SYSTEM_USER

2 Likes

you can create a column which populates the user who created the record

tsql functions which capture that information .. ( u can use )
like example : session_user
-- there may be others

hope this helps :slight_smile: :slight_smile:

example ...
example : SESSION_USER  
DECLARE @session_usr nchar(30);  
SET @session_usr = SESSION_USER;  
SELECT 'This session''s current user is: '+ @session_usr;  
GO
Another Example

1 Like

I thought Session User would return the current login on SQL, not on the frontend...
How can I add the column to the table then?
Tried on a new query:

DECLARE @session_usr nchar(30);
SET @session_usr = SESSION_USER;
SELECT 'This session''s current user is: '+ @session_usr;
ALTER dbo.Processos ADD
Username nchar(30) NOT NULL DEFAULT SESSION_USER`

And of course it doesn't work. Don't quite know how to add the column to the existing table...

What kind of front end are you using?

It's kind of a website with php where users access the database to add, list, print records.
Created some users on the table I mentioned for the login on the frontend.
Only I have access to SQL backend.

Is the api running as a specific user ?
Are you using stored procedures or straight up SQL
Can you capture the user identity and send it to your sql statement when inserting data

1 Like

I'm sorry, newbie here so your questions are getting complicated for me :slight_smile:
I'm using PHPMaker to create the frontend for now, as we needed the database faster then the learning curve for hardcoding.
I'm trying as much as possible to get the SQL database the more complete as I can so we can try to implement a better frontend in the future, keeping the most work done as possible on the backend. That's why I asked for help on this, since I also thought it would be the more bullet proof way.
So, I'm a little confused now:

  • Not worth it?
  • Not possible?

I'm sorry I can't seem to be more helpfull on helping me :slight_smile: ...

100% possible. How are you capturing info of logged in users at the web page level, forget database for now?

1 Like

Sorry for the late response.
After your so valid questions, I found on PHPMaker an option to autofill the User field.
Sometimes just talking about it shows us how to search more in the right direction!
For the creation date, which was also a needed feature, I've created a field on the database, witH "getutcdate".
Need to test it all a little more.

Thank you all for your help!