Projekt

Allgemein

Profil

Herunterladen (1,19 KB)

Fehler #505 » remove_custom_variables_validity_after_deleting_parts.sql

Upgradeskript - G. Richardson, 29.06.2022 14:24

 
-- @tag: remove_custom_variables_validity_after_deleting_parts
-- @description: Beim Löschen von Artikeln auch custom_variables_validity löschen
-- @depends: release_3_6_1

-- remove any orphaned entries
DELETE FROM custom_variables_validity
WHERE trans_id NOT IN (
SELECT id
FROM parts
);

CREATE OR REPLACE FUNCTION public.delete_custom_variables_trigger()
RETURNS trigger
LANGUAGE plpgsql
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);
DELETE FROM custom_variables_validity WHERE trans_id = 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;

RETURN old;
END;
$$;
    (1-1/1)