Using the UIC database, write MySQL statements to implement the following triggers: Create a trigger when delete a student, make sure this student info also be deleted from borrow, enroll and contact tables. Create a trigger when delete an instru...-Go服务器开发

Sure! Here are the MySQL statements to implement the triggers you described:

Trigger to delete student information from related tables:

CREATE TRIGGER delete_student_info 
AFTER DELETE ON student 
FOR EACH ROW
BEGIN
    DELETE FROM borrow WHERE s_id = OLD.s_id;
    DELETE FROM enroll WHERE s_id = OLD.s_id;
    DELETE FROM contact WHERE s_id = OLD.s_id;
END;

Trigger to delete instructor information from related tables:

CREATE TRIGGER delete_instructor_info 
AFTER DELETE ON instructor 
FOR EACH ROW
BEGIN
    DELETE FROM contact WHERE i_id = OLD.i_id;
    DELETE FROM borrow WHERE i_id = OLD.i_id;
END;

Trigger to check if the student exists in the student table and not in the instructor table before inserting into enroll:

CREATE TRIGGER insert_student_to_enroll 
BEFORE INSERT ON enroll 
FOR EACH ROW
BEGIN
    IF (NEW.s_id NOT IN (SELECT s_id FROM student)) OR (NEW.s_id IN (SELECT i_id FROM instructor)) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid student ID!';
    END IF;
END;

Trigger to check if the instructor exists in the instructor table and not in the student table before inserting into contact:

CREATE TRIGGER insert_instructor_to_contact 
BEFORE INSERT ON contact 
FOR EACH ROW
BEGIN
    IF (NEW.i_id NOT IN (SELECT i_id FROM instructor)) OR (NEW.i_id IN (SELECT s_id FROM student)) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid instructor ID!';
    END IF;
END;

Trigger on enroll to detect graduates and add graduate info to a graduate table, then delete them from the student table:

CREATE TRIGGER detect_graduates 
AFTER INSERT ON enroll
FOR EACH ROW
BEGIN
    DECLARE total_credits INT;
    
    SELECT SUM(credits) INTO total_credits FROM course WHERE c_code = NEW.c_code;
    
    IF (total_credits >= 130) THEN
        INSERT INTO graduate (id, s_name, yr, p_code, total_credits)
        VALUES (NEW.s_id, (SELECT s_name FROM student WHERE s_id = NEW.s_id), YEAR(CURRENT_DATE), NEW.p_code, total_credits);
        
        DELETE FROM student WHERE s_id = NEW.s_id;
    END IF;
END;

Note: Please make sure to create the graduate table with the appropriate columns before executing the triggers.

内容由零声教学AI助手提供，问题来源于学员提问