diff options
| -rw-r--r-- | db.sql | 47 |
1 files changed, 13 insertions, 34 deletions
@@ -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; |