dba/triggers.sql

   1 -- APMS Trigger Functions
   2
   3
   4 -----------------------------------------------------------------
   5 -- When we insert, update or delete an accttran record, we need
   6 -- a corresponding update to the accountbalance record.
   7 -----------------------------------------------------------------
   8 DROP TRIGGER accttran_iud ON accttran CASCADE;
   9 CREATE or REPLACE FUNCTION update_accttran() RETURNS trigger AS '
  10   DECLARE
  11     junk INT4;
  12   BEGIN
  13
  14     IF TG_OP != ''DELETE'' THEN
  15       IF TG_OP = ''UPDATE'' THEN
  16         IF NEW.entitytype = OLD.entitytype AND NEW.entitycode = OLD.entitycode
  17              AND NEW.accountcode = OLD.accountcode AND NEW.monthcode = OLD.monthcode AND NEW.amount = OLD.amount
  18         THEN
  19           -- Nothing for us to do
  20           RETURN NEW;
  21         END IF;
  22
  23         IF NEW.entitytype = OLD.entitytype AND NEW.entitycode = OLD.entitycode
  24              AND NEW.accountcode = OLD.accountcode AND NEW.monthcode = OLD.monthcode
  25         THEN
  26           -- We also try and delete it, if that is reasonable...
  27           DELETE FROM accountbalance
  28                       WHERE entitytype = OLD.entitytype
  29                         AND entitycode = OLD.entitycode
  30                         AND accountcode = OLD.accountcode
  31                         AND monthcode = OLD.monthcode
  32                         AND balance = OLD.amount - NEW.amount
  33                         AND budget = 0::numeric
  34                         AND revisedbudget = 0::numeric
  35                         AND notecode IS NULL ;
  36           IF NOT FOUND THEN
  37             UPDATE accountbalance SET balance = balance - OLD.amount + NEW.amount
  38                         WHERE entitytype = OLD.entitytype
  39                           AND entitycode = OLD.entitycode
  40                           AND accountcode = OLD.accountcode
  41                           AND monthcode = OLD.monthcode;
  42           END IF;
  43           RETURN NEW;
  44         END IF;
  45       END IF;
  46       -- Check that necessary fields are not null..
  47       IF NEW.amount IS NULL THEN
  48           RAISE EXCEPTION ''accttran amount cannot be null'';
  49       END IF;
  50       IF NEW.monthcode IS NULL THEN
  51           RAISE EXCEPTION ''accttran monthcode cannot be null'';
  52       END IF;
  53       NEW.entitytype := upper(NEW.entitytype);
  54       IF NEW.entitytype IS NULL OR NEW.entitytype NOT IN (''L'', ''P'', ''T'', ''C'', ''J'') THEN
  55           RAISE EXCEPTION ''accttran entitytype must be one of ''''L'''', ''''P'''', ''''T'''', ''''C'''' or ''''J'''' '';
  56       END IF;
  57       IF NEW.entitycode IS NULL THEN
  58           RAISE EXCEPTION ''accttran entitycode cannot be null '';
  59       END IF;
  60       IF NEW.accountcode IS NULL THEN
  61           RAISE EXCEPTION ''accttran accountcode cannot be null '';
  62       END IF;
  63       IF NEW.date IS NULL THEN
  64           RAISE EXCEPTION ''accttran date cannot be null '';
  65       END IF;
  66     END IF;
  67
  68     IF TG_OP = ''DELETE'' OR TG_OP = ''UPDATE'' THEN
  69       -- We also try and delete it, if that is reasonable...
  70       DELETE FROM accountbalance
  71                   WHERE entitytype = OLD.entitytype
  72                     AND entitycode = OLD.entitycode
  73                     AND accountcode = OLD.accountcode
  74                     AND monthcode = OLD.monthcode
  75                     AND balance = OLD.amount
  76                     AND budget = 0::numeric
  77                     AND revisedbudget = 0::numeric
  78                     AND notecode IS NULL ;
  79       IF NOT FOUND THEN
  80         UPDATE accountbalance SET balance = balance - OLD.amount
  81                     WHERE entitytype = OLD.entitytype
  82                       AND entitycode = OLD.entitycode
  83                       AND accountcode = OLD.accountcode
  84                       AND monthcode = OLD.monthcode;
  85       END IF;
  86       IF TG_OP = ''DELETE'' THEN
  87         -- We are done!
  88         RETURN OLD;
  89       END IF;
  90     END IF;
  91
  92     SELECT 1 INTO junk FROM accountbalance
  93                   WHERE entitytype = NEW.entitytype
  94                     AND entitycode = NEW.entitycode
  95                     AND accountcode = NEW.accountcode
  96                     AND monthcode = NEW.monthcode;
  97     IF FOUND THEN
  98       -- We also try and delete it, if that is reasonable...
  99       DELETE FROM accountbalance
 100                   WHERE entitytype = NEW.entitytype
 101                     AND entitycode = NEW.entitycode
 102                     AND accountcode = NEW.accountcode
 103                     AND monthcode = NEW.monthcode
 104                     AND balance = 0::numeric - NEW.amount
 105                     AND budget = 0::numeric
 106                     AND revisedbudget = 0::numeric
 107                     AND notecode IS NULL ;
 108       IF NOT FOUND THEN
 109         UPDATE accountbalance SET balance = balance + NEW.amount
 110                     WHERE entitytype = NEW.entitytype
 111                       AND entitycode = NEW.entitycode
 112                       AND accountcode = NEW.accountcode
 113                       AND monthcode = NEW.monthcode;
 114       END IF;
 115     ELSE
 116       INSERT INTO accountbalance (entitytype, entitycode, accountcode, monthcode, balance, budget, revisedbudget)
 117                       VALUES(NEW.entitytype, NEW.entitycode, NEW.accountcode, NEW.monthcode, NEW.amount, 0.0, 0.0);
 118     END IF;
 119     RETURN NEW;
 120   END;
 121 ' LANGUAGE plpgsql;
 122
 123 CREATE TRIGGER accttran_iud BEFORE INSERT OR UPDATE OR DELETE ON accttran
 124     FOR EACH ROW EXECUTE PROCEDURE update_accttran();
 125
 126
 127 -----------------------------------------------------------------
 128 -- When we insert, update or delete an accountbalance record, we
 129 -- need a corresponding update to the accountsummary record.
 130 -- Since this procedure is called from within transaction update
 131 -- through the trigger on accttran, special effort has gone into
 132 -- trying to minimise the actual number of database reads/writes.
 133 -- Sometimes that might make it look arcane, like having some
 134 -- pre-emptive DELETEs...
 135 -----------------------------------------------------------------
 136 DROP TRIGGER accountbalance_iud ON accountbalance CASCADE;
 137 CREATE or REPLACE FUNCTION update_accountbalance() RETURNS trigger AS '
 138   DECLARE
 139     junk INT4;
 140   BEGIN
 141
 142     IF TG_OP != ''DELETE'' THEN
 143       IF TG_OP = ''UPDATE'' THEN
 144         IF NEW.entitytype = OLD.entitytype AND NEW.entitycode = OLD.entitycode
 145              AND NEW.accountcode = OLD.accountcode AND NEW.balance = OLD.balance
 146              AND NEW.budget = OLD.budget AND NEW.revisedbudget = OLD.revisedbudget
 147         THEN
 148           -- Nothing for us to do
 149           RETURN NEW;
 150         END IF;
 151
 152         IF NEW.entitytype = OLD.entitytype AND NEW.entitycode = OLD.entitycode
 153              AND NEW.accountcode = OLD.accountcode
 154         THEN
 155           -- We try and delete first, if that is reasonable...
 156           DELETE FROM accountsummary
 157                       WHERE entitytype = OLD.entitytype
 158                         AND entitycode = OLD.entitycode
 159                         AND accountcode = OLD.accountcode
 160                         AND balance = OLD.balance - NEW.balance
 161                         AND budget = OLD.budget - NEW.budget
 162                         AND revisedbudget = OLD.revisedbudget - NEW.revisedbudget
 163                         AND notecode IS NULL ;
 164           IF NOT FOUND THEN
 165             -- Only the balance/budget/revised has changed, so a single update is sufficient
 166             UPDATE accountsummary SET balance = balance - OLD.balance + NEW.balance,
 167                                   budget = budget - OLD.budget + NEW.budget,
 168                                   revisedbudget = revisedbudget - OLD.revisedbudget + NEW.revisedbudget
 169                     WHERE entitytype = OLD.entitytype
 170                       AND entitycode = OLD.entitycode
 171                       AND accountcode = OLD.accountcode;
 172           END IF;
 173           RETURN NEW;
 174         END IF;
 175       END IF;
 176
 177       -- Check that necessary fields are not null..
 178       IF NEW.balance IS NULL THEN
 179           RAISE EXCEPTION ''accountbalance balance cannot be null'';
 180       END IF;
 181       IF NEW.monthcode IS NULL THEN
 182           RAISE EXCEPTION ''accountbalance monthcode cannot be null'';
 183       END IF;
 184       IF NEW.entitytype IS NULL OR NEW.entitytype NOT IN (''L'', ''P'', ''T'', ''C'', ''J'') THEN
 185           RAISE EXCEPTION ''accountbalance entitytype must be one of ''''L'''', ''''P'''', ''''T'''', ''''C'''' or ''''J'''' '';
 186       END IF;
 187       IF NEW.entitycode IS NULL THEN
 188           RAISE EXCEPTION ''accountbalance entitycode cannot be null '';
 189       END IF;
 190       IF NEW.accountcode IS NULL THEN
 191           RAISE EXCEPTION ''accountbalance accountcode cannot be null '';
 192       END IF;
 193     END IF;
 194
 195     IF TG_OP = ''DELETE'' OR TG_OP = ''UPDATE'' THEN
 196       -- We try and delete first, if that is reasonable...
 197       DELETE FROM accountsummary
 198                   WHERE entitytype = OLD.entitytype
 199                     AND entitycode = OLD.entitycode
 200                     AND accountcode = OLD.accountcode
 201                     AND balance = OLD.balance
 202                     AND budget = OLD.budget
 203                     AND revisedbudget = OLD.revisedbudget
 204                     AND notecode IS NULL ;
 205       IF NOT FOUND THEN
 206         -- OK, we have to update it...
 207         UPDATE accountsummary SET balance = balance - OLD.balance,
 208                                   budget = budget - OLD.budget,
 209                                   revisedbudget = revisedbudget - OLD.revisedbudget
 210                     WHERE entitytype = OLD.entitytype
 211                       AND entitycode = OLD.entitycode
 212                       AND accountcode = OLD.accountcode;
 213       END IF;
 214       IF TG_OP = ''DELETE'' THEN
 215         -- We are done!
 216         RETURN OLD;
 217       END IF;
 218     END IF;
 219
 220     SELECT 1 INTO junk FROM accountsummary
 221                   WHERE entitytype = NEW.entitytype
 222                     AND entitycode = NEW.entitycode
 223                     AND accountcode = NEW.accountcode;
 224     IF FOUND THEN
 225       -- We try and delete first, if that is reasonable...
 226       DELETE FROM accountsummary
 227                   WHERE entitytype = NEW.entitytype
 228                     AND entitycode = NEW.entitycode
 229                     AND accountcode = NEW.accountcode
 230                     AND balance = 0::numeric - NEW.balance
 231                     AND budget = 0::numeric - NEW.budget
 232                     AND revisedbudget = 0::numeric - NEW.revisedbudget
 233                     AND notecode IS NULL ;
 234       IF NOT FOUND THEN
 235         UPDATE accountsummary SET balance = balance + NEW.balance,
 236                                   budget = budget + NEW.budget,
 237                                   revisedbudget = revisedbudget + NEW.revisedbudget
 238                     WHERE entitytype = NEW.entitytype
 239                       AND entitycode = NEW.entitycode
 240                       AND accountcode = NEW.accountcode;
 241       END IF;
 242     ELSE
 243       INSERT INTO accountsummary (entitytype, entitycode, accountcode, balance, budget, revisedbudget)
 244              VALUES(NEW.entitytype, NEW.entitycode, NEW.accountcode, NEW.balance, NEW.budget, NEW.revisedbudget);
 245     END IF;
 246     RETURN NEW;
 247   END;
 248 ' LANGUAGE plpgsql;
 249
 250 CREATE TRIGGER accountbalance_iud BEFORE INSERT OR UPDATE OR DELETE ON accountbalance
 251     FOR EACH ROW EXECUTE PROCEDURE update_accountbalance();