4

How can I create a column with a default value of the user that created the row at the time in the table?

I tried system_user, but that shows who is logged in when selecting from the table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Armen Nazarian
  • 95
  • 1
  • 2
  • 5

1 Answers1

9

Try something like this:

CREATE TABLE DemoTable
(
    ID INT IDENTITY(1,1), 
    SomeValue VARCHAR(50), 
    CreatedBy VARCHAR(50) 
        CONSTRAINT DF_DemoTable_CreatedBy DEFAULT(SUSER_NAME())
)

You basically create a default constraint on one of your columns, and you use the SUSER_NAME() function to populate it with the currently logged in user.

When you then insert values into that table:

INSERT INTO dbo.DemoTable(SomeValue) VALUES('Some Value')

and you don't specify an explicit value for CreatedBy, that column is filled with the value from the SUSER_NAME() function, which you can check by selecting from the table:

SELECT * FROM dbo.DemoTable

Read more about SUSER_NAME() and a few related functions (like SUSER_ID()) on TechNet.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thx, That did it! The problem was, I used the computed value functionality.... Question, is there a way of restriction a user from adding the into "CreatedBy" manually.... – Armen Nazarian Nov 18 '13 at 15:48