.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to calculate total at the BackEnd using SqlServer?

Posted By:Syed Shakeer Hussain       Posted Date: August 19, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

How to calculate total at the BackEnd using SqlServer?

In General we take input values what we had entered in textboxes and calculating displaying the result through front end coding.But this may not be secure to your data.so calculation have to be done at the Back end in Sqlserver using Triggers.Trigger will take input of inserted values from a table,after calculating them it insert the result at the particular result colomn.
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

--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


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

The command(s) completed successfully.

Inserting the Input Values through windows Form:

Below image is the Input Design windows form:

Write the Code in 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");


SqlCommand cmd = new SqlCommand("insert into stud_marks(marks1,marks2,rollno) values("+txtmarks1 .Text +","+txtmarks2 .Text +","+txtrollno .Text +")", conn);


//-----------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 Totalmarks Image:

when you click on submi tbutton ,first the values will insert on their corresponding colomns.Later Trigger will fired.It calcultes the marks later total marks result will be inserted using update command.

Now open 'stud_marks' Table  and see the output of Image as Below:

If you want to Know more inforamation on how to create Trigger and uses see the below Link


Thanks For Reading Article!

Syed Shakeer Hussain

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend