How to add the Current Login Details while updating a Row (SQL Server 2005)

Introduction: This article explains how to add the Current Login Details (including, Login ID, Date Time and IP Address) while updating a Row in SQL Server 2005.
Level: Intermediate
Knowledge Required:

• T-SQL
• SQL Server 2005
• Table
• Default Column Value
• User Defined Function

Description:
While saving the data in a Table, we can also save the Login Details like Login ID, Date Time and IP Address with each row. This way we can audit the table. To accomplish this we can also handle it in our insert stored procedure but it is better to use the Default Column Values here, since it is much easier and one time job. However we need to do coding in our Update Stored Procedure.

Default Column Value: While inserting the new Row, if we ignore a column and do NOT include in the Column list then SQL Server puts the Default Value (which we have given) to this column also the Column should be Allow Nulls = False, otherwise SQL Server will put NULL into this column.

For example: we have a Table Student as,

• Student_ID
• Student_Name
• Student_FatherName

If we have created a Front-End application and several users are Inserting/Updating the Students. So we cannot track which user inserted that student and which user updated that student. For this purpose we can add 4 columns in our table as,

• Update_User
• Update_DateTime
• Update_IPAddress
• Update_Count

All fields are Allow Nulls = False

1) To Get the Current Login: SUSER_NAME() Function
2) To Get the Current DateTime: GetDate() Function
3) To Get the IP Address: We will create our own Function

So for the above 2 tasks we have built-in functions, but for the 3rd one we need to create our own function. Fortunately we have discussed this issue in my previous post:

How to get Client IP Address in SQL Server 2005

In the same post we have created a User Defined Function, so we are going to use the same function here.

Thats it, now we only need to put the Default Values for our Columns,

Update_User:
Default Value or Binding: Getdate()

Update_DateTime:
Default Value or Binding: SUSER_NAME()

Update_IPAddress:
Default Value or Binding: dbo.GetCurrentIP()

Update_Count:
Default Value or Binding: 0

Note: To set the Default Value for a column (open table in Design mode)
1) Select the Column (of which you want to set Default Value)
2) Locate the "Default Value or Binding" in the Column Properties (5th property)
3) Type the Value which you want to set as Default

Now open the Table and only type ID, Name and Father Name and refresh the Table you will notice that SQL Server has automatically set the Values for other columns.

Next we will update our Update Stored Procedure as,

CREATE PROCEDURE [dbo].[UpdateStudent] 
    @Student_ID int,
    @Student_Name varchar(255),
    @Student_FatherName varchar(255)
AS
BEGIN 
    SET NOCOUNT ON;

    UPDATE tbl_Student
        SET Student_Name = @Student_Name,
            Student_FatherName = @Student_FatherName,
            Update_User = SUSER_NAME(),
            Update_DateTime = GetDate(),
            Update_IPAddress = dbo.GetCurrentIP(),
            Update_Count = Update_Count + 1
    WHERE
        Student_ID = @Student_ID;
End