MySQL-Triggers
Aus Termserver-CTS2
(Unterschied zwischen Versionen)
Muetzner (Diskussion | Beiträge)
(Die Seite wurde neu angelegt: „ <source lang="mysql"> -- -- Definition of trigger `setLeaf_Insert` -- DROP TRIGGER - →!50030 IF EXISTS: `setLeaf_Insert`; DELIMITER $$ CREATE TRIGGER `setLea…“)
(Die Seite wurde neu angelegt: „ <source lang="mysql"> -- -- Definition of trigger `setLeaf_Insert` -- DROP TRIGGER - →!50030 IF EXISTS: `setLeaf_Insert`; DELIMITER $$ CREATE TRIGGER `setLea…“)
Aktuelle Version vom 7. Oktober 2014, 08:58 Uhr
-- -- Definition of trigger `setLeaf_Insert` -- DROP TRIGGER /*!50030 IF EXISTS */ `setLeaf_Insert`; DELIMITER $$ CREATE TRIGGER `setLeaf_Insert` AFTER INSERT ON `code_system_entity_version_association` FOR EACH ROW UPDATE code_system_entity_version SET isLeaf=false WHERE versionId = NEW.leftId AND NEW.associationKind=2 AND NEW.status=1 $$ DELIMITER ; -- -- Definition of trigger `setLeaf_Update` -- DROP TRIGGER /*!50030 IF EXISTS */ `setLeaf_Update`; DELIMITER $$ CREATE TRIGGER `setLeaf_Update` AFTER UPDATE ON `code_system_entity_version_association` FOR EACH ROW BEGIN DECLARE leafCount numeric default 0; IF NEW.associationKind=2 AND OLD.status=0 AND NEW.status=1 THEN UPDATE code_system_entity_version SET isLeaf=0 WHERE versionId = NEW.leftID; ELSEIF NEW.associationKind=2 AND OLD.status=1 AND NEW.status=0 THEN select count(*) INTO leafCount from code_system_entity_version_association where leftID=NEW.leftID AND status=1 AND associationKind=2; INSERT INTO debug.debug (id,debug_output) values ('setLeaf_Update, count', leafCount); IF leafCount=0 THEN UPDATE code_system_entity_version SET isLeaf=1 WHERE versionId = NEW.leftID; END IF; END IF; END $$ DELIMITER ; -- -- Definition of trigger `setLeaf_Delete` -- DROP TRIGGER /*!50030 IF EXISTS */ `setLeaf_Delete`; DELIMITER $$ CREATE TRIGGER `setLeaf_Delete` AFTER DELETE ON `code_system_entity_version_association` FOR EACH ROW BEGIN DECLARE leafCount numeric default 0; select count(*) INTO leafCount from code_system_entity_version_association where leftID=OLD.leftID AND status=1 AND associationKind=2; IF leafCount=0 THEN UPDATE code_system_entity_version SET isLeaf=1 WHERE versionId = OLD.leftID; END IF; END $$ DELIMITER ;