1delimiter // 2 3DROP TRIGGER IF EXISTS Events_Hour_delete_trigger// 4CREATE TRIGGER Events_Hour_delete_trigger BEFORE DELETE ON Events_Hour 5FOR EACH ROW BEGIN 6 UPDATE Monitors SET 7 HourEvents = COALESCE(HourEvents,1)-1, 8 HourEventDiskSpace=COALESCE(HourEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 9 WHERE Id=OLD.MonitorId; 10END; 11// 12 13DROP TRIGGER IF EXISTS Events_Hour_update_trigger; 14CREATE TRIGGER Events_Hour_update_trigger AFTER UPDATE ON Events_Hour 15FOR EACH ROW 16 BEGIN 17 declare diff BIGINT default 0; 18 19 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 20 IF ( diff ) THEN 21 IF ( NEW.MonitorID != OLD.MonitorID ) THEN 22 UPDATE Monitors SET HourEventDiskSpace=COALESCE(DayEventDiskSpace,0)-OLD.DiskSpace WHERE Monitors.Id=OLD.MonitorId; 23 UPDATE Monitors SET HourEventDiskSpace=COALESCE(DayEventDiskSpace,0)+NEW.DiskSpace WHERE Monitors.Id=NEW.MonitorId; 24 ELSE 25 UPDATE Monitors SET HourEventDiskSpace=COALESCE(DayEventDiskSpace,0)+diff WHERE Monitors.Id=NEW.MonitorId; 26 END IF; 27 END IF; 28END; 29// 30 31DROP TRIGGER IF EXISTS Events_Day_delete_trigger// 32CREATE TRIGGER Events_Day_delete_trigger BEFORE DELETE ON Events_Day 33FOR EACH ROW BEGIN 34 UPDATE Monitors SET 35 DayEvents = COALESCE(DayEvents,1)-1, 36 DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 37 WHERE Id=OLD.MonitorId; 38END; 39// 40 41DROP TRIGGER IF EXISTS Events_Day_update_trigger; 42CREATE TRIGGER Events_Day_update_trigger AFTER UPDATE ON Events_Day 43FOR EACH ROW 44 BEGIN 45 declare diff BIGINT default 0; 46 47 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 48 IF ( diff ) THEN 49 IF ( NEW.MonitorID != OLD.MonitorID ) THEN 50 UPDATE Monitors SET DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)-OLD.DiskSpace WHERE Monitors.Id=OLD.MonitorId; 51 UPDATE Monitors SET DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)+NEW.DiskSpace WHERE Monitors.Id=NEW.MonitorId; 52 ELSE 53 UPDATE Monitors SET DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)+diff WHERE Monitors.Id=NEW.MonitorId; 54 END IF; 55 END IF; 56END; 57// 58 59DROP TRIGGER IF EXISTS Events_Week_delete_trigger// 60CREATE TRIGGER Events_Week_delete_trigger BEFORE DELETE ON Events_Week 61FOR EACH ROW BEGIN 62 UPDATE Monitors SET 63 WeekEvents = COALESCE(WeekEvents,1)-1, 64 WeekEventDiskSpace=COALESCE(WeekEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 65 WHERE Id=OLD.MonitorId; 66END; 67// 68 69DROP TRIGGER IF EXISTS Events_Week_update_trigger; 70CREATE TRIGGER Events_Week_update_trigger AFTER UPDATE ON Events_Week 71FOR EACH ROW 72 BEGIN 73 declare diff BIGINT default 0; 74 75 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 76 IF ( diff ) THEN 77 IF ( NEW.MonitorID != OLD.MonitorID ) THEN 78 UPDATE Monitors SET WeekEventDiskSpace=COALESCE(WeekEventDiskSpace,0)-OLD.DiskSpace WHERE Monitors.Id=OLD.MonitorId; 79 UPDATE Monitors SET WeekEventDiskSpace=COALESCE(WeekEventDiskSpace,0)+NEW.DiskSpace WHERE Monitors.Id=NEW.MonitorId; 80 ELSE 81 UPDATE Monitors SET WeekEventDiskSpace=COALESCE(WeekEventDiskSpace,0)+diff WHERE Monitors.Id=NEW.MonitorId; 82 END IF; 83 END IF; 84END; 85// 86 87DROP TRIGGER IF EXISTS Events_Month_delete_trigger// 88CREATE TRIGGER Events_Month_delete_trigger BEFORE DELETE ON Events_Month 89FOR EACH ROW BEGIN 90 UPDATE Monitors SET 91 MonthEvents = COALESCE(MonthEvents,1)-1, 92 MonthEventDiskSpace=COALESCE(MonthEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 93 WHERE Id=OLD.MonitorId; 94END; 95// 96 97DROP TRIGGER IF EXISTS Events_Month_update_trigger; 98CREATE TRIGGER Events_Month_update_trigger AFTER UPDATE ON Events_Month 99FOR EACH ROW 100 BEGIN 101 declare diff BIGINT default 0; 102 103 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 104 IF ( diff ) THEN 105 IF ( NEW.MonitorID != OLD.MonitorID ) THEN 106 UPDATE Monitors SET MonthEventDiskSpace=COALESCE(MonthEventDiskSpace,0)-OLD.DiskSpace WHERE Monitors.Id=OLD.MonitorId; 107 UPDATE Monitors SET MonthEventDiskSpace=COALESCE(MonthEventDiskSpace,0)+NEW.DiskSpace WHERE Monitors.Id=NEW.MonitorId; 108 ELSE 109 UPDATE Monitors SET MonthEventDiskSpace=COALESCE(MonthEventDiskSpace,0)+diff WHERE Monitors.Id=NEW.MonitorId; 110 END IF; 111 END IF; 112END; 113// 114 115DROP TRIGGER IF EXISTS event_insert_trigger// 116 117CREATE TRIGGER event_insert_trigger AFTER INSERT ON Events 118FOR EACH ROW 119 BEGIN 120 INSERT INTO Events_Hour (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 121 INSERT INTO Events_Day (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 122 INSERT INTO Events_Week (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 123 INSERT INTO Events_Month (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 124 UPDATE Monitors SET 125 HourEvents = COALESCE(HourEvents,0)+1, 126 DayEvents = COALESCE(DayEvents,0)+1, 127 WeekEvents = COALESCE(WeekEvents,0)+1, 128 MonthEvents = COALESCE(MonthEvents,0)+1, 129 TotalEvents = COALESCE(TotalEvents,0)+1 130 WHERE Id=NEW.MonitorId; 131 END; 132 // 133 134delimiter ; 135 136UPDATE Monitors SET 137TotalEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id), 138TotalEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND DiskSpace IS NOT NULL), 139HourEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB( NOW(), INTERVAL 1 hour) ), 140HourEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 hour) AND DiskSpace IS NOT NULL), 141DayEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 day)), 142DayEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 day) AND DiskSpace IS NOT NULL), 143WeekEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 week)), 144WeekEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 week) AND DiskSpace IS NOT NULL), 145MonthEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB( NOW(), INTERVAL 1 month)), 146MonthEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 month) AND DiskSpace IS NOT NULL), 147ArchivedEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND Archived=1), 148ArchivedEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND Archived=1 AND DiskSpace IS NOT NULL); 149