Open-ILS/src/sql/Pg/upgrade/0950.schema.materialized_billing_summary_delete_trigger.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0950', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION money.materialized_summary_billing_del () RETURNS TRIGGER AS $$
   6 DECLARE
   7         prev_billing    money.billing%ROWTYPE;
   8         old_billing     money.billing%ROWTYPE;
   9 BEGIN
  10         SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
  11         SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
  12
  13         IF OLD.id = old_billing.id THEN
  14                 UPDATE  money.materialized_billable_xact_summary
  15                   SET   last_billing_ts = prev_billing.billing_ts,
  16                         last_billing_note = prev_billing.note,
  17                         last_billing_type = prev_billing.billing_type
  18                   WHERE id = OLD.xact;
  19         END IF;
  20
  21         IF NOT OLD.voided THEN
  22                 UPDATE  money.materialized_billable_xact_summary
  23                   SET   total_owed = total_owed - OLD.amount,
  24                         balance_owed = balance_owed - OLD.amount
  25                   WHERE id = OLD.xact;
  26         END IF;
  27
  28         RETURN OLD;
  29 END;
  30 $$ LANGUAGE PLPGSQL;
  31
  32 COMMIT;