1delimiter // 2 3DROP TRIGGER IF EXISTS event_insert_trigger// 4 5create trigger event_insert_trigger after insert on Events 6for each row 7 begin 8 9 INSERT INTO Events_Hour (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 10 INSERT INTO Events_Day (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 11 INSERT INTO Events_Week (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 12 INSERT INTO Events_Month (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 13 UPDATE Monitors SET TotalEvents = TotalEvents+1 WHERE Id=NEW.MonitorId; 14end; 15// 16 17UPDATE Monitors INNER JOIN ( 18 SELECT MonitorId, 19 COUNT(Id) AS TotalEvents, 20 SUM(DiskSpace) AS TotalEventDiskSpace, 21 SUM(IF(Archived,1,0)) AS ArchivedEvents, 22 SUM(IF(Archived,DiskSpace,0)) AS ArchivedEventDiskSpace, 23 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 hour),1,0)) AS HourEvents, 24 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 hour),DiskSpace,0)) AS HourEventDiskSpace, 25 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 day),1,0)) AS DayEvents, 26 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 day),DiskSpace,0)) AS DayEventDiskSpace, 27 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 week),1,0)) AS WeekEvents, 28 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 week),DiskSpace,0)) AS WeekEventDiskSpace, 29 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 month),1,0)) AS MonthEvents, 30 SUM(IF(StartTime > DATE_SUB(NOW(), INTERVAL 1 month),DiskSpace,0)) AS MonthEventDiskSpace 31 FROM Events GROUP BY MonitorId 32 ) AS E ON E.MonitorId=Monitors.Id SET 33 Monitors.TotalEvents = E.TotalEvents, 34 Monitors.TotalEventDiskSpace = E.TotalEventDiskSpace, 35 Monitors.ArchivedEvents = E.ArchivedEvents, 36 Monitors.ArchivedEventDiskSpace = E.ArchivedEventDiskSpace, 37 Monitors.HourEvents = E.HourEvents, 38 Monitors.HourEventDiskSpace = E.HourEventDiskSpace, 39 Monitors.DayEvents = E.DayEvents, 40 Monitors.DayEventDiskSpace = E.DayEventDiskSpace, 41 Monitors.WeekEvents = E.WeekEvents, 42 Monitors.WeekEventDiskSpace = E.WeekEventDiskSpace, 43 Monitors.MonthEvents = E.MonthEvents, 44 Monitors.MonthEventDiskSpace = E.MonthEventDiskSpace// 45 46