DROP TRIGGER change_lists_update;
DELIMITER $$
CREATE TRIGGER change_lists_update
before update ON `student_address` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       set @serial_scy = (select max(serial_scy) from school_year WHERE activities_scy LIKE '%RESET_TRAVEL_FORMAT_UPDATE_ADDRESS%');
      set @serial_lst = (select max(serial_lst) from list WHERE alias_lst ='TRAVEL_EXPORTED'); 
       SELECT 1
       INTO @id_exists
       FROM list_student_year
       WHERE list_student_year.serial_std= NEW.serial_std and list_student_year.serial_lst= @serial_lst and list_student_year.serial_scy=@serial_scy;
		
       IF OLD.main_street_sta!=NEW.main_street_sta ||  OLD.number_sta!=NEW.number_sta ||  OLD.secondary_street_sta!=NEW.secondary_street_sta ||  OLD.status_sta!=NEW.status_sta ||  OLD.serial_par!=NEW.serial_par ||  OLD.serial_cnt!=NEW.serial_cnt ||  OLD.serial_ste!=NEW.serial_ste 
       THEN
           UPDATE list_student_year
           SET acept_lsy = 'NO'
           WHERE list_student_year.serial_std = NEW.serial_std  AND list_student_year.serial_lst= @serial_lst and list_student_year.serial_scy=@serial_scy;
        END IF;
        
       -- COMMIT;
END;
$$
DELIMITER ;



DROP TRIGGER change_lists_insert;
DELIMITER $$
CREATE TRIGGER change_lists_insert
AFTER insert ON `student_address` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       set @serial_scy = (select max(serial_scy) from school_year WHERE activities_scy LIKE '%RESET_TRAVEL_FORMAT_UPDATE_ADDRESS%');
      set @serial_lst = (select max(serial_lst) from list WHERE alias_lst ='TRAVEL_EXPORTED'); 
       SELECT 1
       INTO @id_exists
       FROM list_student_year
       WHERE list_student_year.serial_std= NEW.serial_std and list_student_year.serial_lst= @serial_lst and list_student_year.serial_scy=@serial_scy;
		
       IF @id_exists = 1
       THEN
           UPDATE list_student_year
           SET acept_lsy = 'NO'
           WHERE list_student_year.serial_std = NEW.serial_std  AND list_student_year.serial_lst= @serial_lst and list_student_year.serial_scy=@serial_scy;
        END IF;
END;
$$
DELIMITER ;