consolidate the FK triggers

1 files changed, 13 insertions, 34 deletions

diff --git a/db.sql b/db.sql
index 00438ec..17d9883 100644
--- a/db.sql
+++ b/db.sql
@@ -44,48 +44,28 @@ CREATE TABLE package_rel_map (
 
 -- before insert on the package relation mapping, catch invalid references
 
-CREATE TRIGGER fki_package_rel_map_suite_id_suites_id
+CREATE TRIGGER fki_package_rel_map
   BEFORE INSERT ON [package_rel_map]
   FOR EACH ROW BEGIN
-    SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "suites.id"')
-    WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
-  END;
-
-CREATE TRIGGER fki_package_rel_map_package_id_packages_id
-  BEFORE INSERT ON [package_rel_map]
-  FOR EACH ROW BEGIN
-    SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "packages.id"')
-    WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
-  END;
-
-CREATE TRIGGER fki_package_rel_map_component_id_components_id
-  BEFORE INSERT ON [package_rel_map]
-  FOR EACH ROW BEGIN
-    SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "components.id"')
-    WHERE (SELECT id FROM components WHERE id =NEW.component_id) IS NULL;
+    SELECT RAISE(ROLLBACK, 'INSERT FK violation: package_rel_map/suites.id')
+      WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
+    SELECT RAISE(ROLLBACK, 'INSERT FK violation: package_rel_map/packages.id')
+      WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
+    SELECT RAISE(ROLLBACK, 'INSERT FK violation: package_rel_map/components.id')
+      WHERE (SELECT id FROM components WHERE id =NEW.component_id) IS NULL;
   END;
 
 -- the same but for updates
 
-CREATE TRIGGER fku_package_rel_map_suite_id_suites_id
+CREATE TRIGGER fku_package_rel_map
   BEFORE UPDATE ON [package_rel_map]
   FOR EACH ROW BEGIN
-    SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "suites.id"')
-      WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
-  END;
-
-CREATE TRIGGER fku_package_rel_map_package_id_packages_id
-  BEFORE UPDATE ON [package_rel_map]
-  FOR EACH ROW BEGIN
-    SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "packages.id"')
+    SELECT RAISE(ROLLBACK, 'UPDATE FK violation: package_rel_map/packages.id')
       WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
-  END;
-
-CREATE TRIGGER fku_package_rel_map_component_id_components_id
-  BEFORE UPDATE ON [package_rel_map]
-  FOR EACH ROW BEGIN
-    SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "components.id"')
+    SELECT RAISE(ROLLBACK, 'UPDATE FK violation: package_rel_map/components.id')
      WHERE (SELECT id FROM components WHERE id=NEW.component_id) IS NULL;
+    SELECT RAISE(ROLLBACK, 'UPDATE FK violation: package_rel_map/suites.id')
+      WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
   END;
 
 -- when a package/component/suite id is deleted, clean up any references
@@ -117,6 +97,5 @@ CREATE TRIGGER fkdc_packages_id_package_rel_map_package_id
   FOR EACH ROW BEGIN
     DELETE FROM packages WHERE packages.id = OLD.package_id
       AND (SELECT package_id FROM package_rel_map 
-                     WHERE package_id = OLD.package_id AND rowid != OLD.rowid) 
-      IS NULL;
+        WHERE package_id = OLD.package_id AND rowid != OLD.rowid) IS NULL;
   END;