From 7a58d8650c134e7509f447bf3a48a470f9baa551 Mon Sep 17 00:00:00 2001
From: Sean Finney <seanius@debian.org>
Date: Wed, 3 Sep 2008 18:56:20 +0200
Subject: consolidate the FK triggers

---
 db.sql | 47 +++++++++++++----------------------------------
 1 file changed, 13 insertions(+), 34 deletions(-)

(limited to 'db.sql')

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;
-- 
cgit v1.2.3

