Revision 18448fca
Von Niclas Zimmermann vor etwa 11 Jahren hinzugefügt
| sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl | ||
|---|---|---|
|
# @tag: custom_variables_delete_via_trigger_2
|
||
|
# @description: Benutzerdefinierte Variablen werden nun via Trigger gelöscht (beim Löschen von Kunden, Lieferanten, Kontaktpersonen, Waren, Dienstleistungen, Erzeugnissen und Projekten).
|
||
|
# @depends: custom_variables_delete_via_trigger
|
||
|
|
||
|
package SL::DBUpgrade2::custom_variables_delete_via_trigger_2;
|
||
|
|
||
|
use utf8;
|
||
|
use strict;
|
||
|
|
||
|
use parent qw(SL::DBUpgrade2::Base);
|
||
|
|
||
|
sub run {
|
||
|
my ($self) = @_;
|
||
|
|
||
|
my @queries = (
|
||
|
#Delete orphaned entries
|
||
|
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id NOT IN (SELECT id FROM customer UNION SELECT id FROM vendor)
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'|,
|
||
|
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id NOT IN (SELECT id FROM contacts)
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'|,
|
||
|
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id NOT IN (SELECT id FROM parts)
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'|,
|
||
|
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id NOT IN (SELECT id FROM project)
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'|,
|
||
|
|
||
|
#Create trigger
|
||
|
q|CREATE OR REPLACE FUNCTION delete_cv_custom_variables_trigger() RETURNS trigger AS $$
|
||
|
BEGIN
|
||
|
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id = OLD.id
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT';
|
||
|
|
||
|
RETURN OLD;
|
||
|
END;
|
||
|
$$ LANGUAGE plpgsql|,
|
||
|
|
||
|
q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON customer|,
|
||
|
q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON vendor|,
|
||
|
|
||
|
q|CREATE TRIGGER delete_cv_custom_variables
|
||
|
BEFORE DELETE ON customer
|
||
|
FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|,
|
||
|
q|CREATE TRIGGER delete_cv_custom_variables
|
||
|
BEFORE DELETE ON vendor
|
||
|
FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|,
|
||
|
|
||
|
#Create trigger
|
||
|
q|CREATE OR REPLACE FUNCTION delete_contact_custom_variables_trigger() RETURNS trigger AS $$
|
||
|
BEGIN
|
||
|
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id = OLD.cp_id
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts';
|
||
|
|
||
|
RETURN OLD;
|
||
|
END;
|
||
|
$$ LANGUAGE plpgsql|,
|
||
|
|
||
|
q|DROP TRIGGER IF EXISTS delete_contact_custom_variables ON contacts|,
|
||
|
|
||
|
q|CREATE TRIGGER delete_contact_custom_variables
|
||
|
BEFORE DELETE ON contacts
|
||
|
FOR EACH ROW EXECUTE PROCEDURE delete_contact_custom_variables_trigger()|,
|
||
|
|
||
|
#Create trigger
|
||
|
q|CREATE OR REPLACE FUNCTION delete_part_custom_variables_trigger() RETURNS trigger AS $$
|
||
|
BEGIN
|
||
|
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id = OLD.id
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC';
|
||
|
|
||
|
RETURN OLD;
|
||
|
END;
|
||
|
$$ LANGUAGE plpgsql|,
|
||
|
|
||
|
q|DROP TRIGGER IF EXISTS delete_part_custom_variables ON parts|,
|
||
|
|
||
|
q|CREATE TRIGGER delete_part_custom_variables
|
||
|
BEFORE DELETE ON parts
|
||
|
FOR EACH ROW EXECUTE PROCEDURE delete_part_custom_variables_trigger()|,
|
||
|
|
||
|
#Create trigger
|
||
|
q|CREATE OR REPLACE FUNCTION delete_project_custom_variables_trigger() RETURNS trigger AS $$
|
||
|
BEGIN
|
||
|
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
|
||
|
AND trans_id = OLD.id
|
||
|
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects';
|
||
|
|
||
|
RETURN OLD;
|
||
|
END;
|
||
|
$$ LANGUAGE plpgsql|,
|
||
|
|
||
|
q|DROP TRIGGER IF EXISTS delete_project_custom_variables ON project|,
|
||
|
|
||
|
q|CREATE TRIGGER delete_project_custom_variables
|
||
|
BEFORE DELETE ON project
|
||
|
FOR EACH ROW EXECUTE PROCEDURE delete_project_custom_variables_trigger()|,
|
||
|
|
||
|
);
|
||
|
|
||
|
$self->db_query($_) for @queries;
|
||
|
|
||
|
return 1;
|
||
|
}
|
||
|
|
||
|
1;
|
||
Auch abrufbar als: Unified diff
Noch mehr Trigger für Benutzervariablen
Beim Löschen von Projekten, Ansprechpersonen, Kunden, Lieferanten,
Waren, Erzeugnissen und Dienstleistungen werden jetzt auch die
benutzerdefinierten Variablen gelöscht.