MySQL Trigger | MySQL Before/After Insert Trigger | MySQL Before/After Update Trigger | MySQL Before/After Delete Trigger

MySQL Trigger | MySQL Before/After Insert Trigger | MySQL Before/After Update Trigger | MySQL Before/After Delete Trigger:

DROP TRIGGER IF EXISTS `example_table_before_insert`;
DELIMITER $$
CREATE TRIGGER `example_table_before_insert`
BEFORE INSERT ON `example_table` FOR EACH ROW BEGIN
  DECLARE ready INT DEFAULT 0;
  DECLARE rnd_str TEXT;
  IF NEW.CODE IS NULL OR CHAR_LENGTH(NEW.CODE) = 0 THEN
    WHILE NOT ready DO
      SET rnd_str := UPPER(SUBSTR(MD5(CONCAT(rand(), now())), 1, 5));
      IF NOT exists(SELECT * FROM example_table WHERE CODE = rnd_str) THEN
        SET NEW.CODE = rnd_str;
        SET ready := 1;
      END IF;
    END WHILE;
  END IF;
END
$$
DELIMITER ;

DROP TRIGGER IF EXISTS `example_table_before_update`;
DELIMITER $$
CREATE TRIGGER `example_table_before_update`
BEFORE UPDATE ON `example_table` FOR EACH ROW BEGIN
  DECLARE ready INT DEFAULT 0;
  DECLARE rnd_str TEXT;
  IF NEW.CODE IS NULL OR CHAR_LENGTH(NEW.CODE) = 0 THEN
    WHILE NOT ready DO
      SET rnd_str := UPPER(SUBSTR(MD5(CONCAT(rand(), now())), 1, 5));
      IF NOT exists(SELECT * FROM example_table WHERE CODE = rnd_str) THEN
        SET NEW.CODE = rnd_str;
        SET ready := 1;
      END IF;
    END WHILE;
  END IF;
  SET NEW.updated_at = now();
END
$$
DELIMITER ;

DROP TRIGGER IF EXISTS `example_table_after_delete`;
DELIMITER $$
CREATE TRIGGER `example_table_after_delete`
AFTER DELETE ON `example_table` FOR EACH ROW BEGIN
  DELETE FROM example_table_associations WHERE example_id=OLD.id;
END
$$
DELIMITER ;