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 ;
No comments:
Post a Comment