MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `status` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `systemname` char(40) DEFAULT NULL,
  `status` char(40) DEFAULT NULL,
  `lastchecked` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8
CREATE TABLE `updowncount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `upcount` int DEFAULT NULL,
  `downcount` int DEFAULT NULL,
  `systemname` text,
  `lastupdate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
 CREATE TRIGGER `countsystemadd` BEFORE INSERT ON `status`
 FOR EACH ROW BEGIN
       SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`);
       IF @COUNT=0 THEN
        INSERT INTO `updowncount` (upcount, downcount, systemname, `lastupdate`) VALUES (0, 0, NEW.`systemname`, NOW());
       END IF;
END
 CREATE TRIGGER `countupdate` AFTER INSERT ON `status`
 FOR EACH ROW BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`);
       IF @COUNT > 0 THEN 
        IF NEW.`status` = 'UP'
        THEN 
        UPDATE `updowncount`
        SET `upcount` = `upcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        ELSE
        UPDATE `updowncount`
        SET `downcount` = `downcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        END IF;
    END IF;
 END
INSERT INTO status (`systemname`,`status`) VALUES  ('SystemA','UP')
SELECT * FROM updowncount


id
upcount
downcount
systemname
lastupdate


18
1
0
SystemA
2024-01-22 13:24:19