MySQL-Triggers

--
-- 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 ;