From 493457086f727b713cca06c25c1c3bedb92185af Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet-services.de>
Date: Tue, 15 Jan 2013 14:46:30 +0100
Subject: [PATCH] =?utf8?q?Nach=20L=C3=B6schen=20in=20ar/ap/gl=20via=20DB-T?=
 =?utf8?q?rigger=20in=20acc=5Ftrans=20aufr=C3=A4umen?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 SL/AP.pm                                      |  5 +----
 SL/AR.pm                                      |  4 +---
 SL/GL.pm                                      |  5 ++---
 ...elete_triggers_deletion_from_acc_trans.sql | 22 +++++++++++++++++++
 4 files changed, 26 insertions(+), 10 deletions(-)
 create mode 100644 sql/Pg-upgrade2/ar_ap_gl_delete_triggers_deletion_from_acc_trans.sql

diff --git a/SL/AP.pm b/SL/AP.pm
index 95bde38e5..3c8e2404f 100644
--- a/SL/AP.pm
+++ b/SL/AP.pm
@@ -395,13 +395,10 @@ sub delete_transaction {
   # connect to database
   my $dbh = $form->dbconnect_noauto($myconfig);
 
+  # acc_trans entries are deleted by database triggers.
   my $query = qq|DELETE FROM ap WHERE id = ?|;
   do_query($form, $dbh, $query, $form->{id});
 
-  $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
-  do_query($form, $dbh, $query, $form->{id});
-
-  # commit and redirect
   my $rc = $dbh->commit;
   $dbh->disconnect;
 
diff --git a/SL/AR.pm b/SL/AR.pm
index 57739188a..26c0bca1e 100644
--- a/SL/AR.pm
+++ b/SL/AR.pm
@@ -417,12 +417,10 @@ sub delete_transaction {
   # connect to database, turn AutoCommit off
   my $dbh = $form->dbconnect_noauto($myconfig);
 
+  # acc_trans entries are deleted by database triggers.
   my $query = qq|DELETE FROM ar WHERE id = ?|;
   do_query($form, $dbh, $query, $form->{id});
 
-  $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
-  do_query($form, $dbh, $query, $form->{id});
-
   # commit
   my $rc = $dbh->commit;
   $dbh->disconnect;
diff --git a/SL/GL.pm b/SL/GL.pm
index c9fcd9c47..7bcb47509 100644
--- a/SL/GL.pm
+++ b/SL/GL.pm
@@ -51,9 +51,8 @@ sub delete_transaction {
   # connect to database
   my $dbh = $form->dbconnect_noauto($myconfig);
 
-  my @values = (conv_i($form->{id}));
-  do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
-  do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, @values);
+  # acc_trans entries are deleted by database triggers.
+  do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($form->{id}));
 
   # commit and redirect
   my $rc = $dbh->commit;
diff --git a/sql/Pg-upgrade2/ar_ap_gl_delete_triggers_deletion_from_acc_trans.sql b/sql/Pg-upgrade2/ar_ap_gl_delete_triggers_deletion_from_acc_trans.sql
new file mode 100644
index 000000000..e38e58c34
--- /dev/null
+++ b/sql/Pg-upgrade2/ar_ap_gl_delete_triggers_deletion_from_acc_trans.sql
@@ -0,0 +1,22 @@
+-- @tag: ar_ap_gl_delete_triggers_deletion_from_acc_trans
+-- @description: Beim Löschen aus ar, ap, gl per Trigger auch dazugehörige Einträge aus acc_trans löschen
+-- @depends: release_3_0_0
+-- @charset: utf-8
+CREATE OR REPLACE FUNCTION clean_up_acc_trans_after_ar_ap_gl_delete() RETURNS trigger AS $$
+  BEGIN
+    DELETE FROM acc_trans WHERE trans_id = OLD.id;
+    RETURN OLD;
+  END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER after_delete_ar_trigger
+AFTER DELETE ON ar FOR EACH ROW EXECUTE
+PROCEDURE clean_up_acc_trans_after_ar_ap_gl_delete();
+
+CREATE TRIGGER after_delete_ap_trigger
+AFTER DELETE ON ap FOR EACH ROW EXECUTE
+PROCEDURE clean_up_acc_trans_after_ar_ap_gl_delete();
+
+CREATE TRIGGER after_delete_gl_trigger
+AFTER DELETE ON gl FOR EACH ROW EXECUTE
+PROCEDURE clean_up_acc_trans_after_ar_ap_gl_delete();
-- 
2.20.1