How to calculate total at the BackEnd using Trigger?

Syedshakeer
Posted by in Sql Server category on for Intermediate level | Views : 8112 red flag

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

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");

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

 

Thanks For Reading my Article!

Syed Shakeer Hussain

Page copy protected against web site content infringement by Copyscape

About the Author

Syedshakeer
Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
http://www.dotnetfunda.com
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of www.dotnetspider.com.He has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)