Question

HW3.STUDENT Id First:yarchar10) yarchar(5) Last: yareh 10) HW3.CLASS ClassId: yarchar(5) Name: yareh 10) Desc: varchar(20) HW3.CLASSREQ Classld: yarchar5) Prereald: yarcha(5) Coreg: char(1) eg. T or F HW3.SCHEDULE Studentld: yarcha(5) Classld: yarchar(5) Semester: char(1) Year e.g. (S for Spring, M for Summer, and F for Fall) e.g. (2013, 2014,...) int Here are some the possible class names and their p re-req & co-req Class ID 10000 0001 10002 10003 20000 20001 30000 Class NVame CS46A CS46B CS47 CS49J CS146 CS157A MATH46 Pre-req Co-req CS46A or CS49J MATH46 CS46B CS46B CS146 You would need to add constraints and other checks to the above definitions to accomplish the following tasks: 4). (40 pts) Disallow students to take a class without a pre-requisite or co-requisite. (Use trigger)

Answer 1

Hi,
here is the full code of trigger with exception, i couldn't add the coReq condtion, because coReg is declared as char(1) in above table, where is the id of the coreq class comign from? If you can give that i can add it,
CREATE TRIGGER prevent_class_insertion --trigger definitions
BEFORE INSERT ON customers --to be triggered before insert
FOR EACH ROW
DECLARE
CURSOR c_classIds IS select * from SCHEDULE where studentId=OLD.studentId and classId in(select PrereqId from CLASSREQ where classId=NEW.classId)--selecting if there are any rows with that student taking the prerequisite classes
BEGIN
open c_classIds;
if c_classIds%notfound then--if no rows found means he didnt take one of the pre requisute class
SIGNAL 'pre requisite course not taken'--signal stops the insertion
end if
CLOSE c_classIds;
END;

Thumbs up if this was helpful, otherwise let me know in comments