1 # @tag: custom_variables_delete_via_trigger_2
2 # @description: Benutzerdefinierte Variablen werden nun via Trigger gelöscht (beim Löschen von Kunden, Lieferanten, Kontaktpersonen, Waren, Dienstleistungen, Erzeugnissen und Projekten).
3 # @depends: custom_variables_delete_via_trigger
5 package SL::DBUpgrade2::custom_variables_delete_via_trigger_2;
10 use parent qw(SL::DBUpgrade2::Base);
16 #Delete orphaned entries
17 q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
18 AND trans_id NOT IN (SELECT id FROM customer UNION SELECT id FROM vendor)
19 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'|,
20 q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
21 AND trans_id NOT IN (SELECT id FROM contacts)
22 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'|,
23 q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
24 AND trans_id NOT IN (SELECT id FROM parts)
25 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'|,
26 q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
27 AND trans_id NOT IN (SELECT id FROM project)
28 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'|,
31 q|CREATE OR REPLACE FUNCTION delete_cv_custom_variables_trigger() RETURNS trigger AS $$
33 DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
35 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT';
41 q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON customer|,
42 q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON vendor|,
44 q|CREATE TRIGGER delete_cv_custom_variables
45 BEFORE DELETE ON customer
46 FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|,
47 q|CREATE TRIGGER delete_cv_custom_variables
48 BEFORE DELETE ON vendor
49 FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|,
52 q|CREATE OR REPLACE FUNCTION delete_contact_custom_variables_trigger() RETURNS trigger AS $$
54 DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
55 AND trans_id = OLD.cp_id
56 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts';
62 q|DROP TRIGGER IF EXISTS delete_contact_custom_variables ON contacts|,
64 q|CREATE TRIGGER delete_contact_custom_variables
65 BEFORE DELETE ON contacts
66 FOR EACH ROW EXECUTE PROCEDURE delete_contact_custom_variables_trigger()|,
69 q|CREATE OR REPLACE FUNCTION delete_part_custom_variables_trigger() RETURNS trigger AS $$
71 DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
73 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC';
79 q|DROP TRIGGER IF EXISTS delete_part_custom_variables ON parts|,
81 q|CREATE TRIGGER delete_part_custom_variables
82 BEFORE DELETE ON parts
83 FOR EACH ROW EXECUTE PROCEDURE delete_part_custom_variables_trigger()|,
86 q|CREATE OR REPLACE FUNCTION delete_project_custom_variables_trigger() RETURNS trigger AS $$
88 DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
90 AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects';
96 q|DROP TRIGGER IF EXISTS delete_project_custom_variables ON project|,
98 q|CREATE TRIGGER delete_project_custom_variables
99 BEFORE DELETE ON project
100 FOR EACH ROW EXECUTE PROCEDURE delete_project_custom_variables_trigger()|,
104 $self->db_query($_) for @queries;