How to add row to a SQL Server table where username can be inserted
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
April 8, 2008 10:32 PM
UPDATED:
April 9, 2008 12:24 AM
Answer Wiki:
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.
<pre>CREATE TRIGGER t_SomeTable_i ON SomeTable
FOR INSERT
AS
UPDATE SomeTable
SET YourColumn = suser_sname()
WHERE PrimaryKey in (SELECT PrimaryKey FROM inserted)
GO</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.