5.5

Show how to enforce the constraint “an instructor cannot teach two different sections in a semester in the same time slot.” using a trigger (remember that the constraint can be violated by changes to the teaches relation as well as to to the section relation).

---

CREATE TRIGGER onesec BEFORE INSERT ON section
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (
    nrow.time_slot_id IN (
        SELECT time_slot_id
        FROM teaches NATURAL JOIN section
        WHERE id IN (
            -- this selects ids of all instructors that teach
            -- the inserted section.
            SELECT id
            FROM teaches NATURAL JOIN section
            WHERE sec_id = nrow.sec_id AND 
                course_id = nrow.course_id AND
                semester = nrow.semester AND 
                year = nrow.year
        )
    )
)
BEGIN
    ROLLBACK
END;

CREATE TRIGGER oneteach BEFORE INSERT ON teaches 
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (
    EXISTS (
        SELECT time_slot_id
        FROM teaches NATURAL JOIN section
        WHERE id = nrow.id

        INTERSECT

        SELECT time_slot_id
        FROM section
        WHERE sec_id = nrow.sec_id AND course_id = nrow.course_id AND 
            semester = nrow.semester AND year = nrow.year
    )
)
BEGIN 
    ROLLBACK
END;