How to add row to a SQL Server table where username can be inserted

Tags:
SQL Server 2005
Say I have a table called "Customer" (SQL Server 2005) and 10 users have access rights to this table. If I want to add a row to this SQL Server table to insert the username who initially modified this row, how can I accomplish that task?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You’ve got a couple of options.

  1. Setup a default constraint on the table which puts the value of the suser_sname() function into the column. This will put the name of the person who inserted the row into the table, providing that they do not statically put a value into the column.
  2. If the table is already setup and you don’t want to add the constraint (the constraint is preferred) you can setup a trigger which updates the table with the value of suser_sname.
CREATE TRIGGER t_SomeTable_i ON SomeTable
FOR INSERT
AS
UPDATE SomeTable
SET YourColumn = suser_sname()
WHERE PrimaryKey in (SELECT PrimaryKey FROM inserted)
GO

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following