Database Reference
In-Depth Information
Figure 7.18
Test constraint trigger.
is rolled back. To verify if the trigger works, run the code in Figure 7.18. Since the student with
the id 18 has not taken the prerequisite for the course ISC4301 speciied by the class id 1005, the
insert operation is rolled back.
7.4.2.2 Implementing Dynamic Application Logic
Here, we will create a trigger to calculate the cumulative GPA whenever a student and his/her
class information is inserted or updated in the table STUDENT_CLASS. In this trigger, we will
assign the A grade the number 4.0, B grade the number 3.0, C grade the number 2.0, D grade the
number 1.0, and F grade the number 0.0. hen, the trigger will calculate the average for all the
classes completed by the student. he calculation result will be displayed on the screen. he code
to implement this application logic is given below:
CREATE TRIGGER CalculateGPA
ON STUDENT_CLASS AFTER INSERT, UPDATE
AS
BEGIN
SELECT S.StudentId, AVG(CASE S.Grade
WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
WHEN 'F' THEN 0.0
WHEN NULL THEN 0.0 END) [Cumulative GPA]
FROM STUDENT_CLASS S, INSERTED I
GROUP BY S.StudentID, I.StudentID
HAVING S.StudentID
=
I.StudentID
END
In the above code, an AFTER trigger is created on the table STUDENT_CLASS. he
built-in function AVG is used to calculate the cumulative GPA. he function AVG takes
the result returned by a CASE operator as the input parameter. he CASE operator is used
to convert letter grades to decimal numbers. Because the aggregate function AVG is in the
SELECT clause with the column StudentID, GROUP BY is used to match the GPA of each
student. he HAVING clause is used to match the student id in the STUDENT_CLASS
table with the student id just inserted. To test the trigger, run the UPDATE statement given
in Figure 7.19.
