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