Changeset 672

Timestamp:

05/18/07 21:02:52 (6 years ago)

Author:

ggpolo

Message:

Created INSERT triggers

Location:

branch/ggpolo/test/umitdb

Files:

• ToDo (added)
• sqlite-schema-incomplete.sql (modified) (5 diffs)

branch/ggpolo/test/umitdb/sqlite-schema-incomplete.sql

@@ -21 +21 @@
 
 -------------------
--- Missing Triggers
--------------------
-
-
+-- Missing UPDATE and DELETE Triggers
+-------------------
+
+
+-------------------
 -- Scan
 -------------------
@@ -67 +68 @@
 );
 
+-------------------
+-- Scan Triggers
+-------------------
+
+-- Triggers for preventing bad insertion on scan
+CREATE TRIGGER scan_insert_bad_scanner
+    BEFORE INSERT ON scan
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM scanner WHERE pk = NEW.fk_scanner) IS NULL)
+            THEN RAISE (ABORT, "BAD INSERT on table 'scan', invalid fk_scanner especified")
+        END;
+    END;
+
+CREATE TRIGGER scan_insert_bad_scan_type
+    BEFORE INSERT ON scan
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM scan_type WHERE pk = NEW.fk_type) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'scan', invalid fk_type especified")
+        END;
+    END;
+
+CREATE TRIGGER scan_insert_bad_protocol
+    BEFORE INSERT ON scan
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM protocol WHERE pk = NEW.fk_protocol) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'scan', invalid fk_protocol especified")
+        END;
+    END;
+
+-- Triggers for preventing bad insertion on _scan_host
+CREATE TRIGGER _scan_host_insert_bad_scan
+    BEFORE INSERT ON _scan_host
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM scan WHERE pk = NEW.fk_scan) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_scan_host', invalid fk_scan especified")
+        END;
+    END;
+    
+CREATE TRIGGER _scan_host_insert_bad_host
+    BEFORE INSERT ON _scan_host
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM host WHERE pk = NEW.fk_host) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_scan_host', invalid fk_host specified")
+        END;
+    END;
+
+
 
 -------------------
@@ -96 +149 @@
 );
 
-
 CREATE TABLE hostname (
     pk    INTEGER NOT NULL PRIMARY KEY,
@@ -123 +175 @@
                    REFERENCES port(pk)
 );
+
+-------------------
+-- Host Triggers
+-------------------
+
+-- Trigger for preventing bad insertion on host
+CREATE TRIGGER host_insert_bad_host_state
+    BEFORE INSERT ON host
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM host_state WHERE pk = NEW.fk_host_state) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'host', invalid fk_host_state especified")
+        END;
+    END;
+
+-- Trigger for preventing bad insertion on address
+CREATE TRIGGER address_insert_bad_vendor
+    BEFORE INSERT ON address
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM vendor WHERE pk = NEW.fk_vendor) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'address', invalid fk_vendor especified")
+        END;
+    END;
+
+-- Triggers for preventing bad insertion on _host_hostname
+CREATE TRIGGER _host_hostname_insert_bad_host
+    BEFORE INSERT ON _host_hostname
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM host WHERE pk = NEW.fk_host) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_host_hostname', invalid fk_host especified")
+        END;
+    END;
+
+CREATE TRIGGER _host_hostname_insert_bad_hostname
+    BEFORE INSERT ON _host_hostname
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM hostname WHERE pk = NEW.fk_hostname) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_host_hostname', invalid fk_hostname especified")
+        END;
+    END;
+
+-- Triggers for preventing bad insertion on _host_address
+CREATE TRIGGER _host_address_bad_host
+    BEFORE INSERT ON _host_address
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM host WHERE pk = NEW.fk_host) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_host_address', invalid fk_host especified")
+        END;
+    END;
+
+CREATE TRIGGER _host_address_bad_address
+    BEFORE INSERT ON _host_address
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM address WHERE pk = NEW.fk_address) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_host_address', invalid fk_address especified")
+        END;
+    END;
+
+-- Triggers for preventing bad insertion on _host_port
+CREATE TRIGGER _host_port_bad_host
+    BEFORE INSERT ON _host_port
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM host WHERE pk = NEW.fk_host) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_host_port', invalid fk_host especified")
+        END;
+    END;
+    
+CREATE TRIGGER _host_port_bad_port
+    BEFORE INSERT ON _host_port
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM port WHERE pk = NEW.fk_port) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table '_host_port', invalid fk_port especified")
+        END;
+    END;
 
 
@@ -166 +299 @@
     state  TEXT
 );
+
+-------------------
+-- Port Triggers
+-------------------
+
+-- Triggers for preventing bad insertion on port
+CREATE TRIGGER port_insert_bad_service
+    BEFORE INSERT ON port
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM service WHERE pk = NEW.fk_service) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'port', invalid fk_service especified")
+        END;
+    END;
+    
+CREATE TRIGGER port_insert_bad_protocol
+    BEFORE INSERT ON port
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM protocol WHERE pk = NEW.fk_protocol) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'port', invalid fk_protocol especified")
+        END;
+    END;
+
+CREATE TRIGGER port_insert_bad_port_state
+    BEFORE INSERT ON port
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM port_state WHERE pk = NEW.fk_port_state) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'port', invalid fk_port_state especified")
+        END;
+    END;
+    
+-- Trigger for preventing bad insertion on extraports
+CREATE TRIGGER extraports_insert_bad_port_state
+    BEFORE INSERT ON extraports
+    FOR EACH ROW BEGIN
+        SELECT CASE
+            WHEN ((SELECT pk FROM port_state WHERE pk = NEW.fk_port_state) IS NULL)
+            THEN RAISE (ABORT, "Bad INSERT on table 'extraports', invalid fk_port_state especified")
+        END;
+    END;