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