Introduction

This article explains how to track user activity while doing CRUD operations in a database using triggers. In this application, I will track which user does which action on the following table. For doing this I am passing a user name to trigger but in a real scenario, it is not possible to pass front-end data to a normal trigger.
In this article I am creating a simple student table of the following structure:
  1. CREATE TABLE[dbo]. [tblStudentInfo](
  2. [Sid][int] IDENTITY(1, 1) NOT NULL,
  3. [sName][varchar](50) NOT NULL,
  4. [sAdd][varchar](50) NOT NULL,
  5. [emailId][varchar](50) NOT NULL,
  6. [sRollNo][varchar](50) NOT NULL,
  7. CONSTRAINT[PK_tblStudentInfo] PRIMARY KEY CLUSTERED(
  8. [Sid] ASC
  9. ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
  10. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
  11. ) ON[PRIMARY]
We do a simple CRUD operation in this table and I also create another table to track the user activity on this table.
  1. CREATE TABLE[dbo]. [TriggerData](
  2. [RollNo][varchar](50) NULL,
  3. [InsertedDate][datetime] NULL,
  4. [Command][varchar](max) NOT NULL,
  5. [InsertedBy][nvarchar](256) NULL,
  6. [Action][varchar](50) NULL
  7. ) ON[PRIMARY]
Now I have taken a blank ASP.NET webpage and design that page using the following HTML and ASP code.
  1. <table id="tblUser" class="auto-style1" runat="server">
  2. <tr>
  3. <td>Enter User Name </td>
  4. <td>
  5. <asp:TextBox ID="txtuserName" runat="server"></asp:TextBox>
  6. </td>
  7. <td>
  8. <asp:Button ID="btnContinue" runat="server" OnClick="btnContinue_Click" Text="Continue" />
  9. </td>
  10. </tr>
  11. </table>
  12. <table id="StudentTbl" class="auto-style1" runat="server" visible="false">
  13. <tr>
  14. <td colspan="4">
  15. <center>
  16. <h2>
  17. Student Information Entry</h2>
  18. </center>
  19. </td>
  20. </tr>
  21. <tr>
  22. <td colspan="2">Student Roll Number
  23. </td>
  24. <td>
  25. <asp:TextBox ID="txtRollNo" runat="server"></asp:TextBox>
  26. <asp:Label ID="libid" runat="server" Visible="False"></asp:Label>
  27. <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtRollNo" ErrorMessage="*" ForeColor="Red"></asp:RequiredFieldValidator>
  28. <br />
  29. </td>
  30. <td rowspan="6">
  31. <asp:GridView ID="gvStudent" runat="server" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" CellPadding="4" GridLines="Horizontal" AutoGenerateColumns="False">
  32. <Columns>
  33. <asp:ButtonField ButtonType="Button" CommandName="Select" Text="Select" HeaderText="Select" />
  34. <asp:BoundField DataField="Sid" HeaderText="Id" />
  35. <asp:BoundField DataField="sRollNo" HeaderText="Roll No" />
  36. <asp:BoundField DataField="sName" HeaderText="Name" />
  37. <asp:BoundField DataField="sAdd" HeaderText="Address" />
  38. <asp:BoundField DataField="emailId" HeaderText="EmailId" />
  39. </Columns>
  40. <FooterStyle BackColor="White" ForeColor="#333333" />
  41. <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
  42. <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
  43. <RowStyle BackColor="White" ForeColor="#333333" />
  44. <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
  45. <SortedAscendingCellStyle BackColor="#F7F7F7" />
  46. <SortedAscendingHeaderStyle BackColor="#487575" />
  47. <SortedDescendingCellStyle BackColor="#E5E5E5" />
  48. <SortedDescendingHeaderStyle BackColor="#275353" />
  49. </asp:GridView>
  50. </td>
  51. </tr>
  52. <tr>
  53. <td colspan="2">Student Name
  54. </td>
  55. <td>
  56. <asp:TextBox ID="txtSName" runat="server"></asp:TextBox>
  57. <br />
  58. </td>
  59. </tr>
  60. <tr>
  61. <td colspan="2">Student Address
  62. </td>
  63. <td>
  64. <asp:TextBox ID="txtSAdd" runat="server"></asp:TextBox>
  65. <br />
  66. </td>
  67. </tr>
  68. <tr>
  69. <td colspan="2">Student Email Id
  70. </td>
  71. <td>
  72. <asp:TextBox ID="txtSEId" runat="server"></asp:TextBox>
  73. <br />
  74. </td>
  75. </tr>
  76. <tr>
  77. <td colspan="2">
  78. </td>
  79. <td>
  80. <asp:Label ID="libmsg" runat="server" Visible="False"></asp:Label>
  81. </td>
  82. </tr>
  83. <tr>
  84. <td>
  85. <asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" Text="Insert" Style="height: 26px" />
  86. </td>
  87. <td class="auto-style2">
  88. <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
  89. </td>
  90. <td>
  91. <asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />
  92. </td>
  93. </tr>
  94. </table>
  95. <table id="triggerAction" class="auto-style2" runat="server" visible="false">
  96. <tr>
  97. <td>
  98. <center>
  99. <h2>Action List By <asp:Label ID="lbluser" runat="server"></asp:Label>
  100. </h2>
  101. </center>
  102. </td>
  103. </tr>
  104. <tr>
  105. <td>
  106. <asp:GridView ID="gvTriggerAction" runat="server" BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" CellPadding="4" GridLines="Horizontal" AutoGenerateColumns="False">
  107. <Columns>
  108. <asp:BoundField DataField="RollNo" HeaderText="Roll No" />
  109. <asp:BoundField DataField="Command" HeaderText="Command" />
  110. <asp:BoundField DataField="Action" HeaderText="Action" />
  111. <asp:BoundField DataField="InsertedDate" DataFormatString="{0:dd-MMM-yyyy}" HeaderText="Date" />
  112. </Columns>
  113. <FooterStyle BackColor="White" ForeColor="#333333" />
  114. <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
  115. <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
  116. <RowStyle BackColor="White" ForeColor="#333333" />
  117. <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
  118. <SortedAscendingCellStyle BackColor="#F7F7F7" />
  119. <SortedAscendingHeaderStyle BackColor="#487575" />
  120. <SortedDescendingCellStyle BackColor="#E5E5E5" />
  121. <SortedDescendingHeaderStyle BackColor="#275353" />
  122. </asp:GridView>
  123. </td>
  124. </tr>
  125. </table>
The page will look like:
student information Entry
Now by using the following 3 functions I simply do the CRUD operations with the database table studentInfo.
Insert Student Record
  1. void AddRecordToDb() {
  2. SqlConnection con = new SqlConnection(
  3. System.Configuration
  4. .ConfigurationManager
  5. .ConnectionStrings["DbCS"]
  6. .ConnectionString
  7. );
  8. SqlCommand cmd = new SqlCommand();
  9. con.Open();
  10. SetContext(con, txtuserName.Text.Trim());
  11. try {
  12. cmd.CommandText = "insert into tblStudentInfo (sName,sAdd,emailId,sRollNo)" +
  13. "values (@sName,@sAdd,@emailId,@sRollNo); SELECT SCOPE_IDENTITY();";
  14. cmd.Connection = con;
  15. cmd.Parameters.AddWithValue("@sName", txtSName.Text);
  16. cmd.Parameters.AddWithValue("@sAdd", txtSAdd.Text);
  17. cmd.Parameters.AddWithValue("@emailId", txtSEId.Text);
  18. cmd.Parameters.AddWithValue("@sRollNo", txtRollNo.Text);
  19. int id = Convert.ToInt32(cmd.ExecuteScalar());
  20. libmsg.Visible = true;
  21. libmsg.Text = "Record Inserted At Positon " + id.ToString() + ".";
  22. } catch {
  23. libmsg.Visible = true;
  24. libmsg.Text = "Error..";
  25. }
  27. }
Update Student Record
  1. void UpdateRecordToDb() {
  2. SqlConnection con = new SqlConnection(
  3. System.Configuration
  4. .ConfigurationManager
  5. .ConnectionStrings["DbCS"]
  6. .ConnectionString
  7. );
  8. SqlCommand cmd = new SqlCommand();
  9. con.Open();
  10. SetContext(con, txtuserName.Text.Trim());
  11. cmd.CommandText = "UPDATE tblStudentInfo SET sName = @sName, sAdd = @sAdd ," + "emailId = @emailId , sRollNo = @sRollNo WHERE Sid = @Sid ";
  12. cmd.Connection = con;
  13. int sid = Convert.ToInt32(libid.Text);
  14. string sname = txtSName.Text;
  15. string sadd = txtSAdd.Text;
  16. string seid = txtSEId.Text;
  17. string rol = txtRollNo.Text;
  18. cmd.Parameters.AddWithValue("@Sid", sid);
  19. cmd.Parameters.AddWithValue("@sName", sname);
  20. cmd.Parameters.AddWithValue("@sAdd", sadd);
  21. cmd.Parameters.AddWithValue("@emailId", seid);
  22. cmd.Parameters.AddWithValue("@sRollNo", rol);
  23. cmd.ExecuteNonQuery();
  24. }
Delete Student Record
  1. void DeleteRecordToDb() {
  2. SqlConnection con = new SqlConnection(
  3. System.Configuration.ConfigurationManager
  4. .ConnectionStrings["DbCS"].ConnectionString);
  5. SqlCommand cmd = new SqlCommand();
  6. con.Open();
  7. SetContext(con, txtuserName.Text.Trim());
  8. cmd.CommandText = "DELETE FROM tblStudentInfo WHERE Sid = @Sid ";
  9. cmd.Connection = con;
  10. int sid = Convert.ToInt32(libid.Text);
  11. cmd.Parameters.AddWithValue("@Sid", sid);
  12. cmd.ExecuteNonQuery();
  13. }
When all the three functions have been created, call those functions from their respective button click events.
Now as I told earlier it is not possible to directly send data to trigger from our front end. So for doing that I am setting a value to the context and writing a Stored Procedure to read that context and make that content available for the trigger.
Stored Procedure
  1. Create PROCEDURE[dbo]. [sp_set_context]
  2. @username nvarchar(256)
  3. AS
  4. BEGIN
  5. --SET NOCOUNT ON added to prevent extra result sets from
  6. --interfering with SELECT statements.
  7. SET NOCOUNT ON;
  8. declare @Ctx varbinary(128)
  9. select @Ctx = convert(varbinary(128), @username)
  10. set context_info @Ctx
  11. END
Now I will use the preceding Stored Procedure to pass a data context value to SQL Server. For that I create the following function and call that function before every CRUD operation.
  1. protected virtual void SetContext(IDbConnection conn, string UserName) {
  2. string currentUserName = UserName;
  3. string spName = "sp_set_context";
  4. if (conn != null) {
  5. if (conn.State != ConnectionState.Open)
  6. conn.Open();
  7. IDbCommand cmd = conn.CreateCommand();
  8. cmd.CommandType = CommandType.StoredProcedure;
  9. cmd.CommandText = spName;
  10. IDbDataParameter param = cmd.CreateParameter();
  11. param.ParameterName = "@username";
  12. param.DbType = DbType.String;
  13. param.Size = 255;
  14. param.Value = currentUserName;
  15. cmd.Parameters.Add(param);
  16. cmd.ExecuteNonQuery();
  17. }
  18. }
IDbConnection conn
conn: pass the connection object while calling that function with a username.
Before every CRUD operation, I am calling this function and setting the user name to the context of SQL Server.
Now I will create three separate triggers to do insert, update and delete actions, and that trigger will read the context value with user action and store that user name and that action done by the user in another table.
  1. After Insert Trigger
    1. Create TRIGGER[dbo]. [TrackStudentInfo]
    2. ON[dbo]. [tblStudentInfo]
    3. AFTER INSERT
    4. AS
    5. BEGIN
    6. DECLARE @id uniqueidentifier;
    7. Declare @sName varchar(50);
    8. Declare @sAdd varchar(50);
    9. Declare @emailId varchar(50);
    10. Declare @RollNo varchar(50);
    11. Declare @Sid varbinary(128);
    12. Declare @InsertedBy nvarchar(256);
    13. Declare @Command varchar(max);
    15. SELECT @InsertedBy = convert(nvarchar(256), CONTEXT_INFO());
    16. select @RollNo = i.sRollNo from inserted i;
    17. select @sName = i.sName from inserted i;
    18. select @sAdd = i.sAdd from inserted i;
    19. select @emailId = i.emailId from inserted i;
    21. select @Command = 'INSERT INTO [TrakingInfoDb].[dbo].[tblStudentInfo]([sName],[sAdd],[emailId],[sRollNo]) VALUES('
    22. '' +
    23. @sName + ''
    24. ','
    25. '' + @sAdd + ''
    26. ','
    27. '' + @emailId + ''
    28. ','
    29. '' + @RollNo + ''
    30. ');';
    32. DECLARE @data datetime;
    33. select @data = getdate();
    34. Insert TriggerData values(@RollNo, @data, @Command, @InsertedBy, 'Insert')
    36. END
  2. After Update Trigger
    1. CREATE TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[tblStudentInfo]
    2. FOR UPDATE
    3. AS
    4. BEGIN
    5. DECLARE @id uniqueidentifier;
    6. Declare @sName varchar(50);
    7. Declare @sAdd varchar(50);
    8. Declare @emailId varchar(50);
    9. Declare @RollNo varchar(50);
    10. Declare @Sid int;
    11. Declare @InsertedBy nvarchar(256);
    12. Declare @Command varchar(max);
    14. SELECT @InsertedBy = convert(nvarchar(256), CONTEXT_INFO());
    15. select @RollNo=i.sRollNo from inserted i;
    16. select @sName=i.sName from inserted i;
    17. select @sAdd=i.sAdd from inserted i;
    18. select @emailId=i.emailId from inserted i;
    19. select @Sid=i.[Sid] from inserted i;
    21. select @Command='UPDATE [TrakingInfoDb].[dbo].[tblStudentInfo] SET [sName]='''
    22. + @sName+ ''',[sAdd]='''+@sAdd+''',[emailId]='''
    23. +@emailId+''',[sRollNo]='''+@RollNo
    24. +'''WHERE [Sid]='''+cast(@Sid as varchar(20))+''');';
    25. DECLARE @data datetime;
    26. select @data =getdate();
    27. Insert TriggerData values(@RollNo,@data,@Command,@InsertedBy,'After Update')
    29. END
  3. After Delete Trigger
    1. ALTER TRIGGER [dbo].[trgAfterDelete] ON [dbo].[tblStudentInfo]
    2. AFTER DELETE
    3. AS
    4. BEGIN
    5. Declare @RollNo varchar(50);
    6. Declare @Sid int;
    7. Declare @InsertedBy nvarchar(256);
    8. Declare @Command varchar(max);
    10. select @RollNo=d.sRollNo from deleted d;
    11. select @Sid=d.[Sid] from deleted d;
    12. SELECT @InsertedBy = convert(nvarchar(256), CONTEXT_INFO());
    13. select @Command='DELETE FROM [TrakingInfoDb].[dbo].[tblStudentInfo]'
    14. +' WHERE [Sid]='''+cast(@Sid as varchar(20))+''';';
    15. DECLARE @data datetime;
    16. select @data =getdate();
    17. Insert TriggerData values(@RollNo,@data,@Command,@InsertedBy,'After Delete')
    19. END
Once we have done this everything is done; run the web page and you will get output like this:
user name
Enter the user name to do a CRUD operation and click the Continue button.
student information Entry data form
The first grid will show the record in the student info table and all the action down by the user Manish will be listed below in descending order. For organized code please find the attachment.

Summary

This article showed how to track user information using a trigger. Please post your feedback, question, or comments about this article.