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:
- CREATE TABLE[dbo]. [tblStudentInfo](
- [Sid][int] IDENTITY(1, 1) NOT NULL,
- [sName][varchar](50) NOT NULL,
- [sAdd][varchar](50) NOT NULL,
- [emailId][varchar](50) NOT NULL,
- [sRollNo][varchar](50) NOT NULL,
- CONSTRAINT[PK_tblStudentInfo] PRIMARY KEY CLUSTERED(
- [Sid] ASC
- ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
- IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
- ) 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.
- CREATE TABLE[dbo]. [TriggerData](
- [RollNo][varchar](50) NULL,
- [InsertedDate][datetime] NULL,
- [Command][varchar](max) NOT NULL,
- [InsertedBy][nvarchar](256) NULL,
- [Action][varchar](50) NULL
- ) ON[PRIMARY]
Now I have taken a blank ASP.NET webpage and design that page using the following HTML and ASP code.
The page will look like:
Now by using the following 3 functions I simply do the CRUD operations with the database table studentInfo.
Insert Student Record
- void AddRecordToDb() {
- SqlConnection con = new SqlConnection(
- System.Configuration
- .ConfigurationManager
- .ConnectionStrings["DbCS"]
- .ConnectionString
- );
- SqlCommand cmd = new SqlCommand();
- con.Open();
- SetContext(con, txtuserName.Text.Trim());
- try {
- cmd.CommandText = "insert into tblStudentInfo (sName,sAdd,emailId,sRollNo)" +
- "values (@sName,@sAdd,@emailId,@sRollNo); SELECT SCOPE_IDENTITY();";
- cmd.Connection = con;
- cmd.Parameters.AddWithValue("@sName", txtSName.Text);
- cmd.Parameters.AddWithValue("@sAdd", txtSAdd.Text);
- cmd.Parameters.AddWithValue("@emailId", txtSEId.Text);
- cmd.Parameters.AddWithValue("@sRollNo", txtRollNo.Text);
- int id = Convert.ToInt32(cmd.ExecuteScalar());
- libmsg.Visible = true;
- libmsg.Text = "Record Inserted At Positon " + id.ToString() + ".";
- } catch {
- libmsg.Visible = true;
- libmsg.Text = "Error..";
- }
-
- }
Update Student Record
- void UpdateRecordToDb() {
- SqlConnection con = new SqlConnection(
- System.Configuration
- .ConfigurationManager
- .ConnectionStrings["DbCS"]
- .ConnectionString
- );
- SqlCommand cmd = new SqlCommand();
- con.Open();
- SetContext(con, txtuserName.Text.Trim());
- cmd.CommandText = "UPDATE tblStudentInfo SET sName = @sName, sAdd = @sAdd ," + "emailId = @emailId , sRollNo = @sRollNo WHERE Sid = @Sid ";
- cmd.Connection = con;
- int sid = Convert.ToInt32(libid.Text);
- string sname = txtSName.Text;
- string sadd = txtSAdd.Text;
- string seid = txtSEId.Text;
- string rol = txtRollNo.Text;
- cmd.Parameters.AddWithValue("@Sid", sid);
- cmd.Parameters.AddWithValue("@sName", sname);
- cmd.Parameters.AddWithValue("@sAdd", sadd);
- cmd.Parameters.AddWithValue("@emailId", seid);
- cmd.Parameters.AddWithValue("@sRollNo", rol);
- cmd.ExecuteNonQuery();
- }
Delete Student Record
- void DeleteRecordToDb() {
- SqlConnection con = new SqlConnection(
- System.Configuration.ConfigurationManager
- .ConnectionStrings["DbCS"].ConnectionString);
- SqlCommand cmd = new SqlCommand();
- con.Open();
- SetContext(con, txtuserName.Text.Trim());
- cmd.CommandText = "DELETE FROM tblStudentInfo WHERE Sid = @Sid ";
- cmd.Connection = con;
- int sid = Convert.ToInt32(libid.Text);
- cmd.Parameters.AddWithValue("@Sid", sid);
- cmd.ExecuteNonQuery();
- }
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
- Create PROCEDURE[dbo]. [sp_set_context]
- @username nvarchar(256)
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
- declare @Ctx varbinary(128)
- select @Ctx = convert(varbinary(128), @username)
- set context_info @Ctx
- 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.
- protected virtual void SetContext(IDbConnection conn, string UserName) {
- string currentUserName = UserName;
- string spName = "sp_set_context";
- if (conn != null) {
- if (conn.State != ConnectionState.Open)
- conn.Open();
- IDbCommand cmd = conn.CreateCommand();
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = spName;
- IDbDataParameter param = cmd.CreateParameter();
- param.ParameterName = "@username";
- param.DbType = DbType.String;
- param.Size = 255;
- param.Value = currentUserName;
- cmd.Parameters.Add(param);
- cmd.ExecuteNonQuery();
- }
- }
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.
- After Insert Trigger
- Create TRIGGER[dbo]. [TrackStudentInfo]
- ON[dbo]. [tblStudentInfo]
- AFTER INSERT
- AS
- BEGIN
- DECLARE @id uniqueidentifier;
- Declare @sName varchar(50);
- Declare @sAdd varchar(50);
- Declare @emailId varchar(50);
- Declare @RollNo varchar(50);
- Declare @Sid varbinary(128);
- Declare @InsertedBy nvarchar(256);
- Declare @Command varchar(max);
-
- SELECT @InsertedBy = convert(nvarchar(256), CONTEXT_INFO());
- select @RollNo = i.sRollNo from inserted i;
- select @sName = i.sName from inserted i;
- select @sAdd = i.sAdd from inserted i;
- select @emailId = i.emailId from inserted i;
-
- select @Command = 'INSERT INTO [TrakingInfoDb].[dbo].[tblStudentInfo]([sName],[sAdd],[emailId],[sRollNo]) VALUES('
- '' +
- @sName + ''
- ','
- '' + @sAdd + ''
- ','
- '' + @emailId + ''
- ','
- '' + @RollNo + ''
- ');';
-
- DECLARE @data datetime;
- select @data = getdate();
- Insert TriggerData values(@RollNo, @data, @Command, @InsertedBy, 'Insert')
-
- END
- After Update Trigger
- CREATE TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[tblStudentInfo]
- FOR UPDATE
- AS
- BEGIN
- DECLARE @id uniqueidentifier;
- Declare @sName varchar(50);
- Declare @sAdd varchar(50);
- Declare @emailId varchar(50);
- Declare @RollNo varchar(50);
- Declare @Sid int;
- Declare @InsertedBy nvarchar(256);
- Declare @Command varchar(max);
-
- SELECT @InsertedBy = convert(nvarchar(256), CONTEXT_INFO());
- select @RollNo=i.sRollNo from inserted i;
- select @sName=i.sName from inserted i;
- select @sAdd=i.sAdd from inserted i;
- select @emailId=i.emailId from inserted i;
- select @Sid=i.[Sid] from inserted i;
-
- select @Command='UPDATE [TrakingInfoDb].[dbo].[tblStudentInfo] SET [sName]='''
- + @sName+ ''',[sAdd]='''+@sAdd+''',[emailId]='''
- +@emailId+''',[sRollNo]='''+@RollNo
- +'''WHERE [Sid]='''+cast(@Sid as varchar(20))+''');';
- DECLARE @data datetime;
- select @data =getdate();
- Insert TriggerData values(@RollNo,@data,@Command,@InsertedBy,'After Update')
-
- END
- After Delete Trigger
- ALTER TRIGGER [dbo].[trgAfterDelete] ON [dbo].[tblStudentInfo]
- AFTER DELETE
- AS
- BEGIN
- Declare @RollNo varchar(50);
- Declare @Sid int;
- Declare @InsertedBy nvarchar(256);
- Declare @Command varchar(max);
-
- select @RollNo=d.sRollNo from deleted d;
- select @Sid=d.[Sid] from deleted d;
- SELECT @InsertedBy = convert(nvarchar(256), CONTEXT_INFO());
- select @Command='DELETE FROM [TrakingInfoDb].[dbo].[tblStudentInfo]'
- +' WHERE [Sid]='''+cast(@Sid as varchar(20))+''';';
- DECLARE @data datetime;
- select @data =getdate();
- Insert TriggerData values(@RollNo,@data,@Command,@InsertedBy,'After Delete')
-
- END
Once we have done this everything is done; run the web page and you will get output like this:
Enter the user name to do a CRUD operation and click the Continue button.
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.