From bdcb8b6acf829940e1ebbef0feabd2c60461d082 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Tue, 17 Oct 2017 22:10:46 -0500 Subject: [PATCH] LP1724223: Fix inherited FKEY triggers When running these triggers on TG_OP=DELETE they always fail and prevent the delete. Skipping the triggers on DELETE allows things to continue as usual. To test ------- Verify that after applying the patch that you can remove notes and copy tags from copies and copies from buckets and Vandely import queues. Signed-off-by: Jason Boyer Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/070.schema.container.sql | 2 +- Open-ILS/src/sql/Pg/800.fkeys.sql | 6 ++--- .../XXXX.schema.lp1724223-re-inherited.sql | 24 +++++++++++++++++++ 3 files changed, 28 insertions(+), 4 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1724223-re-inherited.sql diff --git a/Open-ILS/src/sql/Pg/070.schema.container.sql b/Open-ILS/src/sql/Pg/070.schema.container.sql index b3a305668c..594dfafe58 100644 --- a/Open-ILS/src/sql/Pg/070.schema.container.sql +++ b/Open-ILS/src/sql/Pg/070.schema.container.sql @@ -75,7 +75,7 @@ END; $f$ LANGUAGE PLPGSQL VOLATILE COST 50; CREATE CONSTRAINT TRIGGER inherit_copy_bucket_item_target_copy_fkey - AFTER UPDATE OR INSERT OR DELETE ON container.copy_bucket_item + AFTER UPDATE OR INSERT ON container.copy_bucket_item DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.container_copy_bucket_item_target_copy_inh_fkey(); diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 13129f5002..5835ee3bdd 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -124,7 +124,7 @@ END; $f$ LANGUAGE PLPGSQL VOLATILE COST 50; CREATE CONSTRAINT TRIGGER inherit_import_item_imported_as_fkey - AFTER UPDATE OR INSERT OR DELETE ON vandelay.import_item + AFTER UPDATE OR INSERT ON vandelay.import_item DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.vandelay_import_item_imported_as_inh_fkey(); ALTER TABLE vandelay.bib_queue ADD CONSTRAINT match_bucket_fkey FOREIGN KEY (match_bucket) REFERENCES container.biblio_record_entry_bucket(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; @@ -142,7 +142,7 @@ END; $f$ LANGUAGE PLPGSQL VOLATILE COST 50; CREATE CONSTRAINT TRIGGER inherit_asset_copy_note_copy_fkey - AFTER UPDATE OR INSERT OR DELETE ON asset.copy_note + AFTER UPDATE OR INSERT ON asset.copy_note DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_note_owning_copy_inh_fkey(); CREATE OR REPLACE FUNCTION evergreen.asset_copy_tag_copy_map_copy_inh_fkey() RETURNS TRIGGER AS $f$ @@ -158,7 +158,7 @@ END; $f$ LANGUAGE PLPGSQL VOLATILE COST 50; CREATE CONSTRAINT TRIGGER inherit_asset_copy_tag_copy_map_copy_fkey - AFTER UPDATE OR INSERT OR DELETE ON asset.copy_tag_copy_map + AFTER UPDATE OR INSERT ON asset.copy_tag_copy_map DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_tag_copy_map_copy_inh_fkey(); ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1724223-re-inherited.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1724223-re-inherited.sql new file mode 100644 index 0000000000..35c368686e --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1724223-re-inherited.sql @@ -0,0 +1,24 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DROP TRIGGER IF EXISTS inherit_copy_bucket_item_target_copy_fkey ON container.copy_bucket_item; +DROP TRIGGER IF EXISTS inherit_import_item_imported_as_fkey ON vandelay.import_item; +DROP TRIGGER IF EXISTS inherit_asset_copy_note_copy_fkey ON asset.copy_note; +DROP TRIGGER inherit_asset_copy_tag_copy_map_copy_fkey ON asset.copy_tag_copy_map; + +CREATE CONSTRAINT TRIGGER inherit_copy_bucket_item_target_copy_fkey + AFTER UPDATE OR INSERT ON container.copy_bucket_item + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.container_copy_bucket_item_target_copy_inh_fkey(); +CREATE CONSTRAINT TRIGGER inherit_import_item_imported_as_fkey + AFTER UPDATE OR INSERT ON vandelay.import_item + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.vandelay_import_item_imported_as_inh_fkey(); +CREATE CONSTRAINT TRIGGER inherit_asset_copy_note_copy_fkey + AFTER UPDATE OR INSERT ON asset.copy_note + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_note_owning_copy_inh_fkey(); +CREATE CONSTRAINT TRIGGER inherit_asset_copy_tag_copy_map_copy_fkey + AFTER UPDATE OR INSERT ON asset.copy_tag_copy_map + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_tag_copy_map_copy_inh_fkey(); + +COMMIT; + -- 2.43.2