DELIMITER $$
DROP TRIGGER IF EXISTS optional_subject_status_trg$$ 
CREATE TRIGGER optional_subject_status_trg BEFORE UPDATE ON optional_subject
FOR EACH ROW
BEGIN
	/*change optional subject level specialty status to INACTIVE 
	when an optional subject is changed to INACTIVE*/
	IF NEW.status_osb = 'INACTIVE' THEN
		UPDATE optional_subject_level_specialty osl
		JOIN school_year scy ON osl.serial_scy = scy.serial_scy 
		SET osl.status_osl = 'INACTIVE'
		WHERE osl.serial_osb = OLD.serial_osb
		AND scy.status_scy LIKE '%NEW%';
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS optional_subject_level_specialty_trg$$ 
CREATE TRIGGER optional_subject_level_specialty_trg BEFORE UPDATE ON optional_subject_level_specialty
FOR EACH ROW
BEGIN
	/*change attendees level optional status to INACTIVE 
	when an optional subject level specialty is changed to INACTIVE*/
	IF NEW.status_osl = 'INACTIVE' THEN
		UPDATE attendees_level_optional atd
		SET atd.status_atd = 'INACTIVE'
		WHERE atd.serial_osl = OLD.serial_osl;
	END IF;
END$$
DELIMITER ;