X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=sql%2FPg-upgrade2%2Fdelete_cvars_on_trans_deletion_add_shipto.sql;fp=sql%2FPg-upgrade2%2Fdelete_cvars_on_trans_deletion_add_shipto.sql;h=d2f887b4368d8b462580d2a87a4e6f27a9a5685c;hb=53593baa211863fbf66540cf1bcc36c8fb37257f;hp=0000000000000000000000000000000000000000;hpb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44;p=kivitendo-erp.git diff --git a/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_add_shipto.sql b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_add_shipto.sql new file mode 100644 index 000000000..d2f887b43 --- /dev/null +++ b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_add_shipto.sql @@ -0,0 +1,53 @@ +-- @tag: delete_cvars_on_trans_deletion_add_shipto +-- @description: Löschen von benutzerdefinierten Variablen via Triggerfunktionen auch für shipto +-- @depends: delete_cvars_on_trans_deletion delete_cvars_on_trans_deletion_fix1 + +-- 1.6 Alle benutzerdefinierten Variablen löschen, für die es keine +-- Einträge in shipto mehr gibt. +DELETE FROM custom_variables WHERE id IN + (SELECT cv.id FROM custom_variables cv LEFT JOIN custom_variable_configs cvc ON (cv.config_id = cvc.id) + WHERE module LIKE 'ShipTo' + AND NOT EXISTS (SELECT shipto_id FROM shipto WHERE shipto_id = cv.trans_id)); + + +-- 2.2. Nun die Funktionen, die als Trigger aufgerufen wird und die +-- entscheidet, wie genau zu löschen ist: +CREATE OR REPLACE FUNCTION delete_custom_variables_trigger() +RETURNS TRIGGER AS $$ + BEGIN + IF (TG_TABLE_NAME IN ('orderitems', 'delivery_order_items', 'invoice')) THEN + PERFORM delete_custom_variables_with_sub_module('IC', TG_TABLE_NAME, old.id); + END IF; + + IF (TG_TABLE_NAME = 'parts') THEN + PERFORM delete_custom_variables_with_sub_module('IC', '', old.id); + END IF; + + IF (TG_TABLE_NAME IN ('customer', 'vendor')) THEN + PERFORM delete_custom_variables_with_sub_module('CT', '', old.id); + END IF; + + IF (TG_TABLE_NAME = 'contacts') THEN + PERFORM delete_custom_variables_with_sub_module('Contacts', '', old.cp_id); + END IF; + + IF (TG_TABLE_NAME = 'project') THEN + PERFORM delete_custom_variables_with_sub_module('Projects', '', old.id); + END IF; + + IF (TG_TABLE_NAME = 'shipto') THEN + PERFORM delete_custom_variables_with_sub_module('ShipTo', '', old.shipto_id); + END IF; + + RETURN old; + END; +$$ LANGUAGE plpgsql; + +-- 3. Die eigentlichen Trigger erstellen: + +-- 3.9. shipto +DROP TRIGGER IF EXISTS shipto_delete_custom_variables_after_deletion ON shipto; + +CREATE TRIGGER shipto_delete_custom_variables_after_deletion +AFTER DELETE ON shipto +FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();