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