Consider the following schema:



Student(Student,Status)

Took(Student,Course)

Course(Course,Credits,Type)

In a Student relation, Status can be 'B' (beginner), 'CPR' (completed program requirements),
and 'EG' (eligible to graduate). In a Course relation, Type can be 'C' (core course)
or 'E' (elective course).

Write the following row-level triggers which monitors insertions into Took:
(a) When a 'CPR' student completes 130 credits, change the student's status to 'EG'.
(b) When a beginner student completes all core ('C') courses plus 3 electives ('E'), change
the status from 'B' to 'CPR'.

Hint: The main thing in constructing such triggers is to rst gure out the conditions in the
WHEN clause. This conditions are similar to complex WHERE clauses. For instance, (b)
involves relational division (recall how to do such things with NOT EXISTS).

(a)



CREATE TRIGGER EligibleToGraduate

AFTER INSERT ON Took

REFERENCING NEW AS N

FOR EACH ROW

WHEN (

130 <= ( SELECT SUM(C.Credits)

FROM Took T, Course C, Student S

WHERE T.Student = N.Student

AND T.Course = C.Course

AND T.Student = S.Student

AND S.Status = 'CPR' )

)

UPDATE Student

SET Status = 'EG'

WHERE N.Student = Student

(b)



CREATE TRIGGER DoneWithProgram

AFTER INSERT ON Took

FOR EACH ROW

REFERENCING NEW AS N

WHEN (

EXISTS ( -- Student has status 'B'

SELECT *

FROM Student S

WHERE N.Student = S.Student

AND S.Status = 'B'

)

AND

NOT EXISTS (

( SELECT C.Course

FROM Course C

WHERE C.Type = 'C' )

EXCEPT

( SELECT T.Course

FROM Took T

WHERE T.Student = N.Student)

)

AND

3 <= ( SELECT COUNT(T.Course)

FROM Took T, Course C

WHERE T.Student = N.Student

AND T.Course = C.Course

AND C.Type = 'E' )

)

UPDATE Student

SET Status = 'CPR'

WHERE N.Student = Student