Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TRIGGER tr_check_req
- AFTER INSERT ON HW3.SCHEDULE
- REFERENCING NEW AS nROW
- FOR EACH ROW MODE DB2SQL
- BEGIN
- DECLARE preReqFound BOOLEAN DEFAULT FALSE;
- FOR v1 AS
- c1 CURSOR FOR
- SELECT * FROM HW3.CLASSREQ WHERE ClassId = nROW.ClassId
- DO
- IF( EXISTS(
- SELECT 1 FROM HW3.SCHEDULE WHERE StudentId = nROW.StudentId AND ClassId = PrereqId AND ( YEAR != nRow.YEAR AND Semester != nRow.Semester) AND Coreq = 'F')
- )
- THEN SET preReqFound = TRUE ;
- END IF;
- IF( EXISTS(
- SELECT 1 FROM HW3.SCHEDULE WHERE StudentId = nROW.StudentId AND ClassId = PrereqId AND Coreq = 'T')
- )
- THEN SET preReqFound = TRUE ;
- END IF;
- END FOR;
- IF preReqFound = TRUE
- THEN SIGNAL SQLSTATE '88888' ('Student does not have prerequisite OR co-requisite');
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement