- SQL Server 2005
- Default Column Value
- User Defined Function
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,
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,
Default Value or Binding: Getdate()
Default Value or Binding: SUSER_NAME()
Default Value or Binding: dbo.GetCurrentIP()
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]
SET NOCOUNT ON;
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
Student_ID = @Student_ID;