DELIMITER $$
DROP TRIGGER IF EXISTS seccion_year_trg$$
CREATE TRIGGER seccion_year_trg BEFORE UPDATE ON section_by_year
FOR EACH ROW
BEGIN
	/*change level section year status to INACTIVE 
	when a section Year is changed to INACTIVE*/
	IF NEW.status_sby = 'INACTIVE' THEN
	UPDATE level_section_year lsy
	SET lsy.status_lsy = 'INACTIVE'
	WHERE lsy.serial_scy = OLD.serial_scy
	AND	lsy.serial_set = OLD.serial_set;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS level_seccion_year_trg$$
CREATE TRIGGER level_seccion_year_trg BEFORE UPDATE ON level_section_year
FOR EACH ROW
BEGIN
	/*change level section year specialty status to INACTIVE 
	when a level section Year is changed to INACTIVE*/
	IF NEW.status_lsy = 'INACTIVE' THEN
		UPDATE level_section_year_specialty lsys
		SET lsys.status_lss = 'INACTIVE'
		WHERE lsys.serial_lvl = OLD.serial_lvl
		AND	lsys.serial_scy = OLD.serial_scy
		AND	lsys.serial_set = OLD.serial_set;
		/*change group hour level status to INACTIVE 
		when a level section Year is changed to INACTIVE*/
		UPDATE group_hour_level ghl
		SET ghl.status_ghl = 'INACTIVE'
		WHERE ghl.serial_lvl = OLD.serial_lvl
		AND	ghl.serial_scy = OLD.serial_scy
		AND	ghl.serial_set = OLD.serial_set;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS specialty_level_seccion_trg$$
CREATE TRIGGER specialty_level_seccion_trg BEFORE UPDATE ON level_section_year_specialty
FOR EACH ROW
BEGIN
	IF NEW.status_lss = 'INACTIVE' THEN
	/*change course status to INACTIVE 
	when a level section Year specialty is changed to INACTIVE*/
		UPDATE course c
		SET c.status_crs = 'INACTIVE'
		WHERE c.serial_spt = OLD.serial_spt
		AND	c.serial_lvl = OLD.serial_lvl
		AND	c.serial_scy = OLD.serial_scy
		AND	c.serial_set = OLD.serial_set;
	/*change dependent subject status to INACTIVE 
	when a level section Year specialty is changed to INACTIVE*/	
		UPDATE subject sbj
		SET sbj.status_sbj = 'INACTIVE'
		WHERE sbj.serial_spt = OLD.serial_spt
		AND	sbj.serial_lvl = OLD.serial_lvl
		AND	sbj.serial_scy = OLD.serial_scy
		AND	sbj.serial_set = OLD.serial_set;
	/*change dependent optional_subject_level status to INACTIVE 
	when a level section Year specialty is changed to INACTIVE*/	
		UPDATE optional_subject_level osl
		SET osl.status_osl = 'INACTIVE'
		WHERE osl.serial_spt = OLD.serial_spt
		AND	osl.serial_lvl = OLD.serial_lvl
		AND	osl.serial_scy = OLD.serial_scy
		AND	osl.serial_set = OLD.serial_set;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS course_trg$$
CREATE TRIGGER course_trg BEFORE UPDATE ON course
FOR EACH ROW
BEGIN
	IF NEW.status_crs = 'INACTIVE' THEN
		/*change dependent course_subject status to INACTIVE 
		when a course is changed to INACTIVE*/
		UPDATE subject_course sbc
		SET sbc.status_sbc = 'INACTIVE'
		WHERE sbc.serial_crs = OLD.serial_crs;
		/*change student by course status to INACTIVE 
		when a course is changed to INACTIVE*/
		UPDATE student_by_course stc
		SET stc.status_stc = 'INACTIVE'
		WHERE stc.serial_crs = OLD.serial_crs;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS subject_trg$$
CREATE TRIGGER subject_trg BEFORE UPDATE ON subject
FOR EACH ROW
BEGIN
	/*change dependent course_subject status to INACTIVE 
	when a subject is changed to INACTIVE*/
	IF NEW.status_sbj = 'INACTIVE' THEN
	UPDATE subject_course sbc
	SET sbc.status_sbc = 'INACTIVE'
	WHERE sbc.serial_sbj = OLD.serial_sbj;
	/*change dependent subject_ministry_equivalence status to INACTIVE 
	when a subject is changed to INACTIVE*/
	UPDATE subject_ministry_equivalence sme
	SET sme.status_sme = 'INACTIVE'
	WHERE sme.serial_csb = OLD.serial_csb
	AND  sme.serial_spt =  OLD.serial_spt
 	AND  sme.serial_lvl =  OLD.serial_lvl
	AND  sme.serial_scy =  OLD.serial_scy
	AND  sme.serial_set =  OLD.serial_set;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS employee_employee_area_trg$$
CREATE TRIGGER employee_employee_area_trg BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
	/*change employee_area status to INACTIVE 
	when an employee status is changed to INACTIVE*/
	IF NEW.status_emp = 'INACTIVE' THEN
		UPDATE employee_area ea
		SET ea.status_eba = 'INACTIVE',
			ea.is_director_eba = 'NO'
		WHERE ea.serial_emp = OLD.serial_emp;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS profile_status_trg$$
CREATE TRIGGER profile_status_trg BEFORE UPDATE ON profile
FOR EACH ROW
BEGIN
	/*change user_profile to INACTIVE if profile is INACTIVE*/
	IF NEW.status_prf = 'INACTIVE' THEN
		UPDATE user_profile upr
		SET upr.status_upr = 'INACTIVE'
		WHERE upr.serial_prf = OLD.serial_prf;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS subject_course_trg$$
CREATE TRIGGER subject_course_trg BEFORE UPDATE ON subject_course
FOR EACH ROW
BEGIN
	/*change subject_course_period to INACTIVE if subject_course is INACTIVE*/
	IF NEW.status_sbc = 'INACTIVE' THEN
		UPDATE subject_course_period scp
		SET scp.status_scp = 'INACTIVE',
			scp.weight_scp = 0
		WHERE scp.serial_sbc = OLD.serial_sbc;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS period_subject_course_period_trg$$
CREATE TRIGGER period_subject_course_period_trg BEFORE UPDATE ON period
FOR EACH ROW
BEGIN
	/*change subject_course_period to INACTIVE if period is INACTIVE*/
	IF NEW.status_prd LIKE '%INACTIVE%' THEN
		UPDATE subject_course_period scp
		SET scp.status_scp = 'INACTIVE',
			scp.weight_scp = 0
		WHERE scp.serial_prd = OLD.serial_prd;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS activity_applied_criterias_trg$$
CREATE TRIGGER activity_applied_criterias_trg BEFORE UPDATE ON activity_applied_criterias
FOR EACH ROW
BEGIN
	/*change student_criteria_grade to INACTIVE if activity_applied_criterias is INACTIVE
	also set de grade NULL*/
	IF NEW.status_aac = 'INACTIVE' THEN
		UPDATE student_criteria_grade scg
		SET scg.status_scg = 'INACTIVE',
		    scg.grade_scg = NULL
		WHERE scg.serial_att = OLD.serial_att
		AND scg.serial_prd = OLD.serial_prd
		AND scg.serial_sbc = OLD.serial_sbc
		AND scg.serial_gct = OLD.serial_gct
		AND scg.serial_act = OLD.serial_act;
	ELSEIF NEW.status_aac = 'ACTIVE' AND  OLD.status_aac != NEW.status_aac THEN
	/*change student_criteria_grade to ACTIVE if activity_applied_criterias is ACTIVE*/
		UPDATE student_criteria_grade scg
		SET scg.status_scg = 'ACTIVE'
		WHERE scg.serial_att = OLD.serial_att
		AND scg.serial_prd = OLD.serial_prd
		AND scg.serial_sbc = OLD.serial_sbc
		AND scg.serial_gct = OLD.serial_gct
		AND scg.serial_act = OLD.serial_act;
	/*change student_criteria_grade set the other criterias,serial_gct != OLD.serial_gct,as incomplete*/
		UPDATE student_criteria_grade scg
		SET scg.is_complete_scg = 'NO'
		WHERE scg.serial_att = OLD.serial_att
		AND scg.serial_prd = OLD.serial_prd
		AND scg.serial_sbc = OLD.serial_sbc
		AND scg.serial_gct != OLD.serial_gct
		AND scg.serial_act = OLD.serial_act;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS opt_activity_applied_criterias_trg$$
CREATE TRIGGER opt_activity_applied_criterias_trg BEFORE UPDATE ON opt_activity_applied_criterias
FOR EACH ROW
BEGIN
	/*change opt_student_criteria_grade to INACTIVE if opt_activity_applied_criterias is INACTIVE
	also set de grade to NULL*/
	IF NEW.status_oaa = 'INACTIVE' THEN
		UPDATE opt_student_criteria_grade osg
		SET osg.status_osg = 'INACTIVE',
		    osg.grade_osg = NULL
		WHERE osg.serial_att = OLD.serial_att
		AND osg.serial_prd = OLD.serial_prd
		AND osg.serial_osb = OLD.serial_osb
		AND osg.serial_gct = OLD.serial_gct
		AND osg.serial_opa = OLD.serial_opa;
	ELSEIF NEW.status_oaa = 'ACTIVE' AND OLD.status_oaa != NEW.status_oaa THEN
	/*change opt_student_criteria_grade to ACTIVE if opt_activity_applied_criterias is ACTIVE*/
		UPDATE opt_student_criteria_grade osg
		SET osg.status_osg = 'ACTIVE'
		WHERE osg.serial_att = OLD.serial_att
		AND osg.serial_prd = OLD.serial_prd
		AND osg.serial_osb = OLD.serial_osb
		AND osg.serial_gct = OLD.serial_gct
		AND osg.serial_opa = OLD.serial_opa;
	/*change opt_student_criteria_grade set the other criterias,serial_gct != OLD.serial_gct,as incomplete*/
		UPDATE opt_student_criteria_grade osg
		SET osg.is_complete_osg = 'NO'
		WHERE osg.serial_att = OLD.serial_att
		AND osg.serial_prd = OLD.serial_prd
		AND osg.serial_osb = OLD.serial_osb
		AND osg.serial_gct != OLD.serial_gct
		AND osg.serial_opa = OLD.serial_opa;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS activity_trg$$
CREATE TRIGGER activity_trg BEFORE UPDATE ON activity
FOR EACH ROW
BEGIN 
	IF NEW.status_act = 'INACTIVE' THEN
		/*change activity_applied_criterias to INACTIVE if activity is INACTIVE*/
		UPDATE activity_applied_criterias aac
		SET aac.status_aac = 'INACTIVE'
		WHERE aac.serial_act = OLD.serial_act;
		/*change activity_attachment to INACTIVE if activity is INACTIVE*/
		UPDATE activity_attachment ath
		SET ath.status_ath = 'INACTIVE'
		WHERE ath.serial_act = OLD.serial_act;
	ELSEIF NEW.status_act = 'ACTIVE' AND OLD.status_act != NEW.status_act THEN
		/*change activity_applied_criterias to ACTIVE if activity is ACTIVE*/
		UPDATE activity_applied_criterias aac
		SET aac.status_aac = 'ACTIVE'
		WHERE aac.serial_act = OLD.serial_act;
		/*change activity_attachment to ACTIVE if activity is ACTIVE*/
		UPDATE activity_attachment ath
		SET ath.status_ath = 'ACTIVE'
		WHERE ath.serial_act = OLD.serial_act
		AND ath.status_ath = 'INACTIVE';
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS opt_activity_trg$$
CREATE TRIGGER opt_activity_trg BEFORE UPDATE ON opt_activity
FOR EACH ROW
BEGIN 
	IF NEW.status_opa = 'INACTIVE' THEN
		/*change opt_activity_applied_criterias to INACTIVE if opt_activity is INACTIVE*/
		UPDATE opt_activity_applied_criterias oaa
		SET oaa.status_oaa = 'INACTIVE'
		WHERE oaa.serial_opa = OLD.serial_opa;
		/*change opt_activity_attachment to INACTIVE if opt_activity is INACTIVE*/
		UPDATE opt_activity_attachment oat
		SET oat.status_oat = 'INACTIVE'
		WHERE oat.serial_opa = OLD.serial_opa;
	ELSEIF NEW.status_opa = 'ACTIVE' AND OLD.status_opa != NEW.status_opa THEN
		/*change opt_activity_applied_criterias to ACTIVE if opt_activity is ACTIVE*/
		UPDATE opt_activity_applied_criterias oaa
		SET oaa.status_oaa = 'ACTIVE'
		WHERE oaa.serial_opa = OLD.serial_opa;
		/*change opt_activity_attachment to ACTIVE if opt_activity is ACTIVE*/
		UPDATE opt_activity_attachment oat
		SET oat.status_oat = 'ACTIVE'
		WHERE oat.serial_opa = OLD.serial_opa
		AND oat.status_oat = 'INACTIVE';
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS optional_subject_trg$$
CREATE TRIGGER optional_subject_trg BEFORE UPDATE ON optional_subject
FOR EACH ROW
BEGIN 
	IF NEW.status_osb = 'INACTIVE' THEN
		/*change optional_subject_level to INACTIVE if optional_subject is INACTIVE*/
		UPDATE optional_subject_level osl
		SET osl.status_osl = 'INACTIVE'
		WHERE osl.serial_osb = OLD.serial_osb;
		/*change optional_subject_period to INACTIVE if optional_subject is INACTIVE*/
		UPDATE optional_subject_period osp
		SET osp.status_osp = 'INACTIVE'
		WHERE osp.serial_osb = OLD.serial_osb;
		/*change student_optional_subject to INACTIVE if optional_subject is INACTIVE*/
		UPDATE student_optional_subject sos
		SET sos.status_sos = 'INACTIVE'
		WHERE sos.serial_osb = OLD.serial_osb;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS year_grade_subject_trg$$
CREATE TRIGGER year_grade_subject_trg BEFORE UPDATE ON year_grade_subject
FOR EACH ROW
BEGIN
	/*change grade_subject_score to INACTIVE if year_grade_subject is INACTIVE*/
	IF NEW.status_ygs LIKE '%INACTIVE%' THEN
		UPDATE grade_subject_score gss
		SET gss.status_gss = 'INACTIVE'
		WHERE gss.serial_ygs = OLD.serial_ygs;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS year_special_grade_trg$$
CREATE TRIGGER year_special_grade_trg BEFORE UPDATE ON year_special_grade
FOR EACH ROW
BEGIN
	/*change student_year_special_grade to INACTIVE if year_special_grade is INACTIVE*/
	IF NEW.status_ysg LIKE '%INACTIVE%' THEN
		UPDATE student_year_special_grade sys
		SET sys.status_sys = 'INACTIVE'
		WHERE sys.serial_ysg = OLD.serial_ysg;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS general_activity_trg$$
CREATE TRIGGER general_activity_trg BEFORE UPDATE ON general_activity
FOR EACH ROW
BEGIN
	/*change general_activity_attendees to INACTIVE if general_activity is INACTIVE*/
	IF NEW.status_gac LIKE '%INACTIVE%' THEN
		UPDATE general_activity_attendees gaa
		SET gaa.status_gaa = 'INACTIVE'
		WHERE gaa.serial_gac = OLD.serial_gac;
	END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS hour_trg$$
CREATE TRIGGER hour_trg BEFORE UPDATE ON `hour`
FOR EACH ROW
BEGIN
	/*change credits_detail status to INACTIVE 
	when an hour is changed to INACTIVE*/
	IF NEW.status_hrs = 'INACTIVE' THEN
		UPDATE credits_detail cdt
		SET cdt.status_cdt = 'INACTIVE'
		WHERE cdt.serial_hrs = OLD.serial_hrs;
	ELSEIF NEW.status_hrs = 'ACTIVE' AND OLD.status_hrs != NEW.status_hrs THEN
		/*change credits_detail status to ACTIVE when an hour is changed to ACTIVE*/
		UPDATE credits_detail cdt
		SET cdt.status_cdt = 'ACTIVE'
		WHERE cdt.serial_hrs = OLD.serial_hrs;
	END IF;
END$$
DELIMITER ;



DELIMITER $$
DROP TRIGGER IF EXISTS invoice_trg$$
CREATE TRIGGER invoice_trg BEFORE UPDATE ON invoice
FOR EACH ROW
BEGIN
	IF NEW.status_phy = 'PAID' THEN
	/*change student_year_debts to PAID 
	when an invoice is changed to PAID*/
		UPDATE  student_year_debts syd
		JOIN invoice_detail invd ON invd.serial_deb = syd.serial_deb AND invd.status_psd='ACTIVE'
		JOIN invoice inv ON inv.serial_phy = invd.serial_phy AND inv.status_phy!='INACTIVE'
		SET syd.status_deb='PAID'
		WHERE inv.serial_phy=OLD.serial_phy;		
	END IF;
END$$
DELIMITER ;


DELIMITER $$
DROP TRIGGER IF EXISTS invoice_non_academic_trg$$
CREATE TRIGGER invoice_non_academic_trg BEFORE UPDATE ON invoice_non_academic
FOR EACH ROW
BEGIN
	IF NEW.status_ina = 'PAID' THEN
	/*change student_year_debts to PAID 
	when an invoice_non_academic is changed to PAID*/
		UPDATE  student_year_debts syd
		JOIN invoice_detail_non_academic invdNA ON invdNA.serial_deb = syd.serial_deb AND invdNA.status_idn='ACTIVE'
		JOIN invoice_non_academic invNA ON invNA.serial_ina = invdNA.serial_ina AND invNA.status_ina!='INACTIVE'
		SET syd.status_deb='PAID'
		WHERE invNA.serial_ina=OLD.serial_ina;		
	END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS profile_process_trg$$
CREATE TRIGGER profile_process_trg AFTER INSERT ON process
FOR EACH ROW
BEGIN
	/*Insert into profile_process the new process to the Admin Profile*/	
	INSERT INTO profile_process 
	VALUES (1,NEW.serial_prc,'ACTIVE');	
	
END$$
DELIMITER ;