Changes between Version 2 and Version 3 of AdvancedDatabaseDevelopment

Timestamp:

08/23/26 20:25:38 (4 days ago)

Author:

231118

Comment:

--

AdvancedDatabaseDevelopment

@@ -78 +78 @@
 FOR EACH ROW
 EXECUTE FUNCTION create_ram_alert();
+}}}
+
+Тригер за автоматско предупредување при висока искористеност на дискот (> 90%):
+
+{{{
+CREATE OR REPLACE FUNCTION create_disk_alert()
+RETURNS TRIGGER AS
+$$
+BEGIN
+  IF NEW.disk_usage > 90 THEN
+    INSERT INTO security_alerts(
+      computer_id, alert_type, severity, description, timestamp, resolved
+    )
+    VALUES (
+      NEW.computer_id, 'High Disk Usage', 'MEDIUM',
+      'Disk usage exceeded 90%', NOW(), false
+    );
+  END IF;
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS disk_alert_trigger ON computer_history;
+CREATE TRIGGER disk_alert_trigger
+AFTER INSERT ON computer_history
+FOR EACH ROW
+EXECUTE FUNCTION create_disk_alert();
+}}}
+
+Тригер (BEFORE UPDATE) кој автоматски го ажурира `updated_at` во `env_settings`
+при секоја промена на поставките:
+
+{{{
+CREATE OR REPLACE FUNCTION set_env_settings_updated_at()
+RETURNS TRIGGER AS
+$$
+BEGIN
+  NEW.updated_at := NOW();
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS env_settings_updated_trigger ON env_settings;
+CREATE TRIGGER env_settings_updated_trigger
+BEFORE UPDATE ON env_settings
+FOR EACH ROW
+EXECUTE FUNCTION set_env_settings_updated_at();
+}}}
+
+Тригер (напреден) кој при нова проверка на достапност со недостапен сервис
+(`service_availability.is_available = false`) автоматски генерира безбедносен
+аларм, поврзувајќи го сервисот со неговиот компјутер:
+
+{{{
+CREATE OR REPLACE FUNCTION create_service_down_alert()
+RETURNS TRIGGER AS
+$$
+DECLARE
+  v_computer_id INTEGER;
+  v_service     TEXT;
+BEGIN
+  IF NEW.is_available = false THEN
+    SELECT computer_id, service_name
+      INTO v_computer_id, v_service
+      FROM network_services
+     WHERE id = NEW.service_id;
+
+    INSERT INTO security_alerts(
+      computer_id, alert_type, severity, description, timestamp, resolved
+    )
+    VALUES (
+      v_computer_id, 'Service Unavailable', 'MEDIUM',
+      'Network service ' || COALESCE(v_service, '?') || ' is unavailable',
+      NOW(), false
+    );
+  END IF;
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS service_down_trigger ON service_availability;
+CREATE TRIGGER service_down_trigger
+AFTER INSERT ON service_availability
+FOR EACH ROW
+EXECUTE FUNCTION create_service_down_alert();
+}}}
+
+Тригер кој при нов перформансен запис го освежува `last_seen` на компјутерот
+(машината се смета за активна штом испраќа податоци):
+
+{{{
+CREATE OR REPLACE FUNCTION touch_computer_last_seen()
+RETURNS TRIGGER AS
+$$
+BEGIN
+  UPDATE computers
+     SET last_seen = COALESCE(NEW.timestamp, NOW())
+   WHERE id = NEW.computer_id;
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS touch_last_seen_trigger ON computer_history;
+CREATE TRIGGER touch_last_seen_trigger
+AFTER INSERT ON computer_history
+FOR EACH ROW
+EXECUTE FUNCTION touch_computer_last_seen();
 }}}