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

RSS Feed