How to calculate total at the Backend using Trigger

In general we take input values entered into TextBoxes then do some calculation and then display the result through front end coding. But this may not be secure for your data. So the calculation needs to be done at the backend in SqlServer using Triggers. A Trigger will take input of inserted values from a table, then after calculating them it inserts the result into a specified result column.

Here I am going to give some input values for calculating the total marks. Below is the image of input values design form.

How to create a trigger for insert statement?

Create trigger total_trigger
on stud_marks
for insert
As
--stud_marks is table name--variables are declaring
Declare @rollno int  
Declare @marks1 int
Declare @marks2 int
Declare @totalmarks int

--storing the inserted record value in a varaible
set @rollno=(select rollno from inserted)
set @marks1=(select marks1 from  inserted)
set @marks2=(select marks2 from  inserted)
--Note above 'inserted' is not a table name.
--calculating the Marks and storing a variable
set @totalmarks= @marks1+@marks2
Update stud_marks set total=@totalmarks where rollno=@rollno
Go

Run the above code in SqlQuery Analyzer, it shows the message as

The command(s) completed successfully.

Inserting the Input Values through windows Form:

The following image is the Input Design windows form:

Write the Code in the Submit Button as follows:

private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=INTHIYAAZ;Initial Catalog=shakeer;User ID=sa;Password=sa");
conn.Open();
SqlCommand cmd = new SqlCommand("insert into stud_marks(marks1,marks2,rollno) values("+txtmarks1 .Text +","+txtmarks2 .Text +","+txtrollno .Text +")", conn);
cmd.ExecuteNonQuery();
//-----------trigger will be fired automatically when the record are inserted
cmd = new SqlCommand("select total from stud_marks where rollno="+txtrollno .Text +"", conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
  {
   txttotal.Text = dr["total"].ToString();
   }
}

 Output of Total marks Image:

When you click on the submit button, first the values will be inserted into their corresponding colomns. Later the Trigger will fire. It calculates the marks then the total marks result will be inserted using an update command.

Now open the 'stud marks' table and see the output as shown in the image below:

Thanks for Reading my article!