Question

11. Consider the following simple database with one table with the following schema: Field | Type Null key | Default | Extra auto_increment ! - I tid | int(4) NO | name varchar(30) | YES | | subji | int(2) | YES | subj2 | int(2) | YES I 1 subj3 | int(2) | YES | total | int(3) | YES PRI NULL | NULL NULL NULL NULL NULL - Transactions INSERT a tuple for a new student with the final numerical grades of three subjects. Write a trigger that will assign the TOTAL attribute in the new tuple to be equal to the TOTAL NUMERICAL VALUE of the three numerical grades. Page 3 of 4

Answer 1

Create Trigger Total_Value   //Creates trigger with name Total_value

after Insert,delete,update on Student    //Performs trigger after insert,update and delete operations on student table.

for each row                       //on each and every row in student table

Begin                         //Beginning of Trgger

   insert into Student(total) values(sum(subj1,subj2,subj3)); // Insert the value which is equal to sum of the values of

                                                         // subj1,subj2,subj3 into the total field.

End              //End of the trigger.