Triggers.sql

CREATE TRIGGER updateLecType
AFTER UPDATE
ON FAC_MEMBER
FOR EACH ROW

BEGIN

	IF NEW.LecType IS NULL
		THEN DELETE FROM RES_ASSIST WHERE RES_ASSIST.PersonID = (OLD.PersonID);
		DELETE FROM ASSIST_PROF WHERE ASSIST_PROF.PersonID = (OLD.PersonID);
		DELETE FROM ASSOC_PROF WHERE ASSOC_PROF.PersonID = (OLD.PersonID);
		DELETE FROM PROFFESOR WHERE PROFFESOR.PersonID = (OLD.PersonID);
		DELETE FROM INSTRUCTOR WHERE INSTRUCTOR.InstID = (OLD.PersonID);
	END IF;

	IF NEW.LecType = 1
		THEN INSERT INTO RES_ASSIST (PersonID) VALUES (NEW.PersonID);
	END IF;

	IF NEW.LecType = 2
		THEN INSERT INTO INSTRUCTOR (InstID) VALUES (NEW.PersonID);
		INSERT INTO ASSIST_PROF (PersonID) VALUES (NEW.PersonID);
		DELETE FROM RES_ASSIST WHERE RES_ASSIST.PersonID = (OLD.PersonID);
	END IF;

	IF NEW.LecType = 3
		THEN INSERT INTO ASSOC_PROF (PersonID) VALUES (NEW.PersonID);
		DELETE FROM ASSIST_PROF WHERE ASSIST_PROF.PersonID = (OLD.PersonID);
	END IF;

	IF NEW.LecType = 4
		THEN INSERT INTO PROFFESOR (PersonID) VALUES (NEW.PersonID);
		DELETE FROM ASSOC_PROF WHERE ASSOC_PROF.PersonID = (OLD.PersonID);
	END IF;

	IF NEW.LecType = 0
		THEN DELETE FROM PROFFESOR WHERE PROFFESOR.PersonID = (OLD.PersonID);
	END IF;

END;



-----------------------------------------------------------------------------------------



CREATE TRIGGER updateCoType
AFTER UPDATE
ON COURSE
FOR EACH ROW

BEGIN

	IF NEW.CoType IS NULL
		THEN DELETE FROM MANDATORY WHERE MANDATORY.CoCode = (OLD.CoCode);
		DELETE FROM TECHNICAL WHERE TECHNICAL.CoCode = (OLD.CoCode);
		DELETE FROM N_TECHNICAL WHERE N_TECHNICAL.CoCode = (OLD.CoCode);
		DELETE FROM OPTIONAL WHERE OPTIONAL.CoCode = (OLD.CoCode);

	END IF;

	IF NEW.CoType = 1
		THEN INSERT INTO MANDATORY (CoCode) VALUES (NEW.CoCode);
	END IF;

	IF NEW.CoType = 2
		THEN INSERT INTO OPTIONAL (CoCode) VALUES (NEW.CoCode);
		INSERT INTO TECHNICAL (CoCode) VALUES (NEW.CoCode);
	END IF;

	IF NEW.CoType = 3
		THEN INSERT INTO OPTIONAL (CoCode) VALUES (NEW.CoCode);
		INSERT INTO N_TECHNICAL (CoCode) VALUES (NEW.CoCode);
	END IF;

END;



-----------------------------------------------------------------------------------------



CREATE TRIGGER deleteStudent
AFTER DELETE
ON STUDENT
FOR EACH ROW

BEGIN

	DELETE FROM PERSON WHERE PERSON.PersonID = (OLD.PersonID);

END;