Database Reference
In-Depth Information
7.4.2 Using Triggers
Now, we will discuss how triggers are used in database management and business logic imple-
mentation through examples. Triggers will be used to enforce business constraints and implement
application logic dynamically to keep database integrity.
7.4.2.1 Validating Business Constraints
Here, we will use a trigger to verify if a student has met the prerequisite requirement before he/she
enrolls in a new class. Before a student and class information can be added to the table STUDENT_
CLASS, an AFTER trigger is used to query the courses that have been taken by the student and
then compare the prerequisite of the course to be taken. If there is a match, add the student and
class id to the table STUDENT_CLASS. If not, print the message to inform the student that he/she
cannot enroll in the class. he SQL statement below shows the deinition of the trigger:
CREATE TRIGGER VerifyPrerequisite
ON STUDENT_CLASS AFTER INSERT, UPDATE
AS
IF(('ISC2301' NOT IN (SELECT C.CourseID
FROM INSERTED I, CLASS C
WHERE I.ClassID
=
C.ClassID))
AND
(SELECT CourseID
FROM (SELECT CourseID
FROM STUDENT_CLASS T, INSERTED I, CLASS C
WHERE T.StudentID
=
I.StudentID AND
T.ClassID
=
C.ClassID) W
WHERE NOT EXISTS (
SELECT Prerequisite
FROM CLASS C, INSERTED I, COURSE_PREREQUISITE P
WHERE C.ClassID
=
I.ClassID AND
C.CourseID
=
W.CourseID AND
W.CourseID
=
P.CourseID)
) IS NULL)
BEGIN
PRINT 'The prerequisite is not fulfilled.'
ROLLBACK
END
In the above code, an IF structure is used to verify if a student has met the required prereq-
uisite. If a student is enrolled in the class that teaches ISC2301, which has no prerequisite, the
trigger will not do anything. Otherwise, the condition after AND in the IF structure will verify
if the prerequisite courses have been taken. To accomplish this task, we use the NOT EXISTS
operator to match the set of courses taken by the student with the set of prerequisite courses. he
subquery in the FROM clause selects all the courses taken by the student. he subquery in the
NOT EXISTS operator selects all prerequisite courses of the course speciied by the class id and
stored in the INSERTED table. If the intersection of the record sets selected by the above two sub-
queries is empty, it means that none of the courses taken by the student matches the prerequisite,
the message 'he prerequisite is not fulilled.' is printed on the screen, and the insert transaction
