<commit_before>/**
 * Creates triggers on various tables in the Ilios database schema.
 */

-- drop existing triggers
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_instructor_post_delete`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_instructor_post_insert`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_learner_post_delete`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_learner_post_insert`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_post_update`;
DROP TRIGGER IF EXISTS `trig_learning_material_post_update`;
DROP TRIGGER IF EXISTS `trig_objective_post_update`;
DROP TRIGGER IF EXISTS `trig_offering_pre_update`;
DROP TRIGGER IF EXISTS `trig_offering_instructor_post_delete`;
DROP TRIGGER IF EXISTS `trig_offering_instructor_post_insert`;
DROP TRIGGER IF EXISTS `trig_offering_learner_post_delete`;
DROP TRIGGER IF EXISTS `trig_offering_learner_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_description_post_delete` ;
DROP TRIGGER IF EXISTS `trig_session_description_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_description_post_update`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_post_delete` ;
DROP TRIGGER IF EXISTS `trig_session_learning_material_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_post_update`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_x_mesh_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_x_mesh_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_pre_update`;
DROP TRIGGER IF EXISTS `trig_session_x_discipline_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_x_discipline_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_x_mesh_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_x_mesh_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_x_objective_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_x_objective_post_insert`;

delimiter $$

-- DELETE trigger on "ilm_session_facet_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_instructor_post_delete` AFTER DELETE ON `ilm_session_facet_instructor` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = OLD.`ilm_session_facet_id`;
END;

-- INSERT trigger on "ilm_session_facet_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_instructor_post_insert` AFTER INSERT ON `ilm_session_facet_instructor` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = NEW.`ilm_session_facet_id`;
END;

-- DELETE trigger on "ilm_session_facet_learner"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_learner_post_delete` AFTER DELETE ON `ilm_session_facet_learner` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = OLD.`ilm_session_facet_id`;
END;

-- INSERT trigger on "ilm_session_facet_learner"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_learner_post_insert` AFTER INSERT ON `ilm_session_facet_learner` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = NEW.`ilm_session_facet_id`;
END;

-- UPDATE trigger on "ilm_session_facet" table
-- Sets the "last_updated_on" to the current time on associated sessions
-- if the given records were modified.
CREATE TRIGGER `trig_ilm_session_facet_post_update` AFTER UPDATE ON `ilm_session_facet`
FOR EACH ROW BEGIN
	IF (NEW.hours <> OLD.hours || NEW.due_date <> OLD.due_date) THEN
		UPDATE `session` SET `session`.`last_updated_on` = NOW()
		WHERE `session`.`ilm_session_facet_id` = NEW.`ilm_session_facet_id`;
	END IF;
END;

-- UPDATE trigger on "learning_material" table
-- Sets the "last_updated_on" to the current time on associated sessions 
-- if the given records were modified.
CREATE TRIGGER `trig_learning_material_post_update` AFTER UPDATE ON `learning_material`
FOR EACH ROW BEGIN
	IF (NEW.`learning_material_status_id` <> OLD.`learning_material_status_id`) THEN
		UPDATE `session` SET `session`.last_updated_on = NOW()
		WHERE `session`.`session_id` IN (
			SELECT `session_id` FROM `session_learning_material`
			WHERE `session_learning_material`.`learning_material_id` = NEW.`learning_material_id`
		);
	END IF;
END;

-- UPDATE trigger on "objective" table
-- Sets the "last_updated_on" to the current time on associated sessions 
-- if the given records were modified.
CREATE TRIGGER `trig_objective_post_update` AFTER UPDATE ON `objective` FOR EACH ROW BEGIN
	IF (NEW.`title` <> OLD.`title` 
		|| NEW.`competency_id` <> OLD.`competency_id`) THEN
		UPDATE `session` SET `session`.`last_updated_on` = NOW()
		WHERE `session`.`session_id` IN (
			SELECT `session_id` FROM `session_x_objective`
			WHERE `session_x_objective`.`objective_id` = NEW.`objective_id`
		);
	END IF;
END;

-- DELETE trigger on "offering_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "offering" table.
CREATE TRIGGER `trig_offering_instructor_post_delete` AFTER DELETE ON `offering_instructor`
FOR EACH ROW BEGIN
	UPDATE `offering` SET `offering`.`last_updated_on` = NOW()
	WHERE `offering`.`offering_id` = OLD.`offering_id`;
END;

-- INSERT trigger on "offering_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "offering" table.
CREATE TRIGGER `trig_offering_instructor_post_insert` AFTER INSERT ON `offering_instructor`
FOR EACH ROW BEGIN
	UPDATE `offering` SET `offering`.`last_updated_on` = NOW()
	WHERE `offering`.`offering_id` = NEW.`offering_id`;
END;<commit_msg>replaced triggers on table offering_instructor with triggers on offering_x_instructor and offering_x_instructor_group.<commit_after>/**
 * Creates triggers on various tables in the Ilios database schema.
 */

-- drop existing triggers
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_instructor_post_delete`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_instructor_post_insert`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_learner_post_delete`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_learner_post_insert`;
DROP TRIGGER IF EXISTS `trig_ilm_session_facet_post_update`;
DROP TRIGGER IF EXISTS `trig_learning_material_post_update`;
DROP TRIGGER IF EXISTS `trig_objective_post_update`;
DROP TRIGGER IF EXISTS `trig_offering_pre_update`;
DROP TRIGGER IF EXISTS `trig_offering_x_instructor_post_delete`;
DROP TRIGGER IF EXISTS `trig_offering_x_instructor_post_insert`;
DROP TRIGGER IF EXISTS `trig_offering_x_instructor_group_post_delete`;
DROP TRIGGER IF EXISTS `trig_offering_x_instructor_group_post_insert`;
DROP TRIGGER IF EXISTS `trig_offering_learner_post_delete`;
DROP TRIGGER IF EXISTS `trig_offering_learner_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_description_post_delete` ;
DROP TRIGGER IF EXISTS `trig_session_description_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_description_post_update`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_post_delete` ;
DROP TRIGGER IF EXISTS `trig_session_learning_material_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_post_update`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_x_mesh_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_learning_material_x_mesh_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_pre_update`;
DROP TRIGGER IF EXISTS `trig_session_x_discipline_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_x_discipline_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_x_mesh_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_x_mesh_post_insert`;
DROP TRIGGER IF EXISTS `trig_session_x_objective_post_delete`;
DROP TRIGGER IF EXISTS `trig_session_x_objective_post_insert`;

delimiter $$

-- DELETE trigger on "ilm_session_facet_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_instructor_post_delete` AFTER DELETE ON `ilm_session_facet_instructor` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = OLD.`ilm_session_facet_id`;
END;

-- INSERT trigger on "ilm_session_facet_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_instructor_post_insert` AFTER INSERT ON `ilm_session_facet_instructor` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = NEW.`ilm_session_facet_id`;
END;

-- DELETE trigger on "ilm_session_facet_learner"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_learner_post_delete` AFTER DELETE ON `ilm_session_facet_learner` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = OLD.`ilm_session_facet_id`;
END;

-- INSERT trigger on "ilm_session_facet_learner"
-- Sets the "last_updated_on" to the current time on the parent record in the "session" table.
CREATE TRIGGER `trig_ilm_session_facet_learner_post_insert` AFTER INSERT ON `ilm_session_facet_learner` 
FOR EACH ROW BEGIN
	UPDATE `session` SET `session`.`last_updated_on` = NOW()
	WHERE `session`.`ilm_session_facet_id` = NEW.`ilm_session_facet_id`;
END;

-- UPDATE trigger on "ilm_session_facet" table
-- Sets the "last_updated_on" to the current time on associated sessions
-- if the given records were modified.
CREATE TRIGGER `trig_ilm_session_facet_post_update` AFTER UPDATE ON `ilm_session_facet`
FOR EACH ROW BEGIN
	IF (NEW.hours <> OLD.hours || NEW.due_date <> OLD.due_date) THEN
		UPDATE `session` SET `session`.`last_updated_on` = NOW()
		WHERE `session`.`ilm_session_facet_id` = NEW.`ilm_session_facet_id`;
	END IF;
END;

-- UPDATE trigger on "learning_material" table
-- Sets the "last_updated_on" to the current time on associated sessions 
-- if the given records were modified.
CREATE TRIGGER `trig_learning_material_post_update` AFTER UPDATE ON `learning_material`
FOR EACH ROW BEGIN
	IF (NEW.`learning_material_status_id` <> OLD.`learning_material_status_id`) THEN
		UPDATE `session` SET `session`.last_updated_on = NOW()
		WHERE `session`.`session_id` IN (
			SELECT `session_id` FROM `session_learning_material`
			WHERE `session_learning_material`.`learning_material_id` = NEW.`learning_material_id`
		);
	END IF;
END;

-- UPDATE trigger on "objective" table
-- Sets the "last_updated_on" to the current time on associated sessions 
-- if the given records were modified.
CREATE TRIGGER `trig_objective_post_update` AFTER UPDATE ON `objective` FOR EACH ROW BEGIN
	IF (NEW.`title` <> OLD.`title` 
		|| NEW.`competency_id` <> OLD.`competency_id`) THEN
		UPDATE `session` SET `session`.`last_updated_on` = NOW()
		WHERE `session`.`session_id` IN (
			SELECT `session_id` FROM `session_x_objective`
			WHERE `session_x_objective`.`objective_id` = NEW.`objective_id`
		);
	END IF;
END;

-- DELETE trigger on "offering_x_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "offering" table.
CREATE TRIGGER `trig_offering_x_instructor_post_delete` AFTER DELETE ON `offering_x_instructor`
FOR EACH ROW BEGIN
    UPDATE `offering` SET `offering`.`last_updated_on` = NOW()
    WHERE `offering`.`offering_id` = OLD.`offering_id`;
END;

-- INSERT trigger on "offering_x_instructor"
-- Sets the "last_updated_on" to the current time on the parent record in the "offering" table.
CREATE TRIGGER `trig_offering_x_instructor_post_insert` AFTER INSERT ON `offering_x_instructor`
FOR EACH ROW BEGIN
    UPDATE `offering` SET `offering`.`last_updated_on` = NOW()
    WHERE `offering`.`offering_id` = NEW.`offering_id`;
END;

-- DELETE trigger on "offering_x_instructor_group"
-- Sets the "last_updated_on" to the current time on the parent record in the "offering" table.
CREATE TRIGGER `trig_offering_x_instructor_group_post_delete` AFTER DELETE ON `offering_x_instructor_group`
FOR EACH ROW BEGIN
    UPDATE `offering` SET `offering`.`last_updated_on` = NOW()
    WHERE `offering`.`offering_id` = OLD.`offering_id`;
END;

-- INSERT trigger on "offering_x_instructor_group"
-- Sets the "last_updated_on" to the current time on the parent record in the "offering" table.
CREATE TRIGGER `trig_offering_x_instructor_group_post_insert` AFTER INSERT ON `offering_x_instructor_group`
FOR EACH ROW BEGIN
    UPDATE `offering` SET `offering`.`last_updated_on` = NOW()
    WHERE `offering`.`offering_id` = NEW.`offering_id`;
END;
{'fraction_non_alphanumeric': 0.08162469790286116, 'fraction_numerical': 0.0, 'mean_word_length': 6.6539379474940334, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5377263', 'n_tokens_mistral': 4819, 'n_tokens_neox': 4502, 'n_words': 1183}