DELIMITER |
CREATE TRIGGER `Trg_Insert_Accounts` AFTER INSERT ON `Accounts` FOR EACH ROW BEGIN
  IF NEW.i_env=1 AND (SELECT 1 FROM Customers WHERE i_customer=NEW.i_customer AND i_rep IN (3,5,9,12)) THEN
  IF NEW.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
        INSERT INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.id,'N');
        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','INSERT',NOW(),CONCAT('Add to ip_customers ',NEW.id));
  ELSE
    CALL INSERT_USER(NEW.id,NEW.h323_password,'INSERT','');
        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','INSERT',NOW(),CONCAT('Add to users ',NEW.id));
  END IF;
  END IF;
  END |

--

CREATE TRIGGER `Trg_Update_Accounts` AFTER UPDATE ON `Accounts` FOR EACH ROW BEGIN
DECLARE is_ip INT DEFAULT 0;
DECLARE todo VARCHAR(10) DEFAULT 0;
IF NEW.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
  SET is_ip = 1;
END IF;
IF NEW.i_env=1 AND OLD.i_env=1 AND (NEW.id <> OLD.id OR NEW.h323_password <> OLD.h323_password) THEN
  SET todo = 'UPDATE';
ELSE
  IF NEW.i_env=1 AND OLD.i_env <> 1 THEN
    SET todo = 'INSERT';
  ELSEIF NEW.i_env <> 1 AND OLD.i_env=1 THEN
    SET todo = 'DELETE';
  END IF;
END IF;
CASE todo
  WHEN 'UPDATE' THEN
    IF is_ip THEN
      UPDATE `ast-replication`.ip_customers SET remote_ip=NEW.id WHERE remote_ip=OLD.id AND is_node='N';
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Update ip_customers set ',OLD.id,' to ',NEW.id));
ELSE
      CALL INSERT_USER(NEW.id,NEW.h323_password,'UPDATE',OLD.id);
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('update user password : ',OLD.id));
END IF;
  WHEN 'INSERT' THEN
    IF is_ip THEN
  INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.id,'N');
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Add to ip_customers ',NEW.id));
ELSE
    CALL INSERT_USER(NEW.id,NEW.h323_password,'INSERT','');
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Add to user ',NEW.id));
END IF;
  WHEN 'DELETE' THEN
    IF is_ip THEN
      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.id AND is_node='N';
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Delete from ip_customers ',OLD.id,' (i_env <> 1) i_env: ',OLD.i_env,' to ',NEW.i_env));
ELSE
      DELETE FROM `ast-replication`.users WHERE username = OLD.id;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Delete from users ',OLD.id,' (i_env <> 1) i_env: ',OLD.i_env,' to ',NEW.i_env));
END IF;
END CASE;
END |

--

CREATE TRIGGER `Trg_Delete_Accounts` AFTER DELETE ON `Accounts` FOR EACH ROW BEGIN
  IF OLD.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
    IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = OLD.id AND is_node='N') THEN
      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.id AND is_node='N';
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','DELETE',NOW(),CONCAT('Delete from ip_customers ',OLD.id));
    END IF;
  ELSE
    IF (SELECT 1 FROM `ast-replication`.users  WHERE username = OLD.id) THEN
      DELETE FROM `ast-replication`.users WHERE username = OLD.id;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','DELETE',NOW(),CONCAT('Delete from users ',OLD.id));
    END IF;
  END IF;
  END |

--

CREATE TRIGGER `Trg_Insert_Connections` AFTER INSERT ON `Connections` FOR EACH ROW BEGIN
  IF NEW.i_env=1 AND NEW.remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND NEW.sip='Y' THEN
    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.remote_ip);
INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','INSERT',NOW(),CONCAT('Add to vendors ',NEW.remote_ip));
  END IF;
END |

--

CREATE TRIGGER `Trg_Update_Connections` AFTER UPDATE ON `Connections` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORED';
DECLARE exist INT default 0;
DECLARE valid INT default 0;
SET exist=IFNULL((SELECT 1 FROM `ast-replication`.vendors  WHERE remote_ip = OLD.remote_ip),0);
IF  NEW.i_env=1 AND NEW.remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND NEW.sip='Y' THEN
  SET valid=1;
END IF;
IF valid = 1 AND exist = 1 THEN
  SET todo = 'UPDATE';
  ELSEIF valid <> 1 AND exist = 1 THEN
    SET todo = 'DELETE';
    ELSEIF valid = 1 AND exist <> 1 THEN
      SET todo = 'INSERT';
END IF;
CASE todo
  WHEN 'UPDATE' THEN
    UPDATE `ast-replication`.vendors SET remote_ip=NEW.remote_ip WHERE remote_ip=OLD.remote_ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Update vendors set ',OLD.remote_ip,' to ',NEW.remote_ip));
  WHEN 'INSERT' THEN
    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.remote_ip);
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Add to vendors ',NEW.remote_ip,' old ip should be *SIP-UA* or NULL -> ',OLD.remote_ip));
  WHEN 'DELETE' THEN
    DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.remote_ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Delete from vendors ',NEW.remote_ip,' OLD.remote_ip -> ',OLD.remote_ip));
  WHEN 'IGNORED' THEN
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ignored ',NEW.remote_ip));
END CASE;
END |

--

CREATE TRIGGER `Trg_Delete_Connections` AFTER DELETE ON `Connections` FOR EACH ROW BEGIN
    IF (SELECT 1 FROM `ast-replication`.vendors  WHERE remote_ip = OLD.remote_ip) THEN
      DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.remote_ip;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','DELETE',NOW(),CONCAT('Delete from vendors ',OLD.remote_ip));
    END IF;
  END |

--

CREATE TRIGGER `Trg_Update_Customers` AFTER UPDATE ON `Customers` FOR EACH ROW BEGIN
  IF OLD.i_rep IN (3,5,9,12) AND NEW.i_rep NOT IN (3,5,9,12) THEN
      CALL UPDATE_CUSTOMER(NEW.i_rep,NEW.i_customer);
  ELSEIF OLD.i_rep NOT IN (3,5,9,12) AND NEW.i_rep IN (3,5,9,12) THEN
      CALL UPDATE_CUSTOMER(NEW.i_rep,NEW.i_customer);
  END IF;
END |

--

DELIMITER |

CREATE TRIGGER `Trg_Insert_Nodes` AFTER INSERT ON `Nodes` FOR EACH ROW BEGIN
  IF NEW.i_env=1 AND NEW.i_node_type=11 AND NEW.name not like '%[Deleted]%' THEN
    INSERT INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.ip,'Y');
    INSERT INTO `ast-replication`.vendors (remote_ip) values (NEW.ip);
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','INSERT',NOW(),CONCAT('Add to ip_customers and vendors ',NEW.ip));
  END IF;
END |

--

CREATE TRIGGER `Trg_Update_Nodes` AFTER UPDATE ON `Nodes` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORED';
DECLARE exist INT default 0;
DECLARE valid INT default 0;
SET exist=IFNULL((SELECT 1 FROM `ast-replication`.ip_customers WHERE remote_ip=OLD.ip AND is_node='Y'),0);
IF NEW.i_node_type=11 AND NEW.name NOT LIKE '%[Deleted]%' AND NEW.i_env=1 AND NEW.ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
  SET valid=1;
END IF;
IF valid = 1 AND exist = 1 THEN
  SET todo = 'UPDATE';
  ELSEIF valid <> 1 AND exist = 1 THEN
    SET todo = 'DELETE';
    ELSEIF valid = 1 AND exist <> 1 THEN
      SET todo = 'INSERT';
END IF;
INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('TEST ',NEW.ip,' valid ',valid,' exist ',exist));
CASE todo
  WHEN 'UPDATE' THEN
    UPDATE `ast-replication`.ip_customers SET remote_ip=NEW.ip WHERE remote_ip=OLD.ip AND is_node='Y';
    UPDATE `ast-replication`.vendors SET remote_ip=NEW.ip WHERE remote_ip=OLD.ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ip_customers and vendors set ',OLD.ip,' to ',NEW.ip));
  WHEN 'INSERT' THEN
    INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.ip,'Y');
    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.ip);
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Add to ip_customers and vendors',NEW.ip));
  WHEN 'DELETE' THEN
    DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.ip AND is_node='Y';
    DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Delete from ip_customers and vendors',NEW.ip));
  WHEN 'IGNORED' THEN
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ignored ',NEW.ip));
END CASE;
END |

--

CREATE TRIGGER `Trg_Delete_Nodes` AFTER DELETE ON `Nodes` FOR EACH ROW BEGIN
    IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = OLD.ip AND is_node='Y') THEN
      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.ip AND is_node='Y';
          DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.ip;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','DELETE',NOW(),CONCAT('Delete from ip_customers and vendors',OLD.ip));
    END IF;
  END |

--

DROP PROCEDURE IF EXISTS `INSERT_USER` |
CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ PROCEDURE `INSERT_USER`(IN account varchar(30), IN secret VARCHAR(80), IN todo VARCHAR(30), IN us VARCHAR(30))
BEGIN
  DECLARE rm VARCHAR(80);
  DECLARE md varchar(32);
  SET rm = (SELECT val FROM `ast-replication`.realm ORDER BY id DESC LIMIT 1);
  SET md = (SELECT md5(CONCAT(account,':',rm,':',secret)));
  IF secret IS NULL THEN
    SET md = (SELECT md5(CONCAT(account,':',rm,':')));
  END IF;
  IF todo LIKE "INSERT" THEN
    INSERT IGNORE INTO `ast-replication`.users (username,md5secret) values (account,md);
  ELSEIF todo LIKE "UPDATE" THEN
    UPDATE `ast-replication`.users SET md5secret=md, username=account WHERE username=us;
  END IF;
  END |
DROP PROCEDURE IF EXISTS `UPDATE_CUSTOMER` |
CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ PROCEDURE `UPDATE_CUSTOMER`(IN irep INT(10) unsigned, IN icustomer INT(10) unsigned)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE account VARCHAR(30);
  DECLARE ienv    VARCHAR(30);
  DECLARE pwd     VARCHAR(30);
  DECLARE cur CURSOR FOR SELECT id,i_env,h323_password FROM Accounts where i_customer=icustomer;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;
  read_loop: LOOP
  FETCH cur INTO account,ienv,pwd ;
  IF done THEN
    LEAVE read_loop;
  END IF;
  IF irep NOT IN (3,5,9,12) THEN
    IF ienv=1 THEN
      IF account REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
        IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = account AND is_node='N') THEN
          DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = account AND is_node='N';
          INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Delete from ip_customers ',account));
        END IF;
      ELSE
        IF (SELECT 1 FROM `ast-replication`.users  WHERE username = account) THEN
          DELETE FROM `ast-replication`.users WHERE username = account;
          INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Delete from users ',account));
        END IF;
      END IF;
END IF;
  ELSE
    IF ienv=1 THEN
  IF account REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
        INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (account,'N');
        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Add to ip_customers ',account));
  ELSE
    CALL INSERT_USER(account,pwd,'INSERT','');
        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Add to users ',account));
  END IF;
END IF;
  END IF;
  END LOOP;
  CLOSE cur;
  END |
DELIMITER ;