PL/pgSQL trigger na predaj ovocia

priklady

PL/pgSQL trigger na predaj ovocia

Jednoduchý systém pre objednávku/rezerváciu tovaru, v tomto prípade ovocia.
Základne podmienky(CHECK):

  • Nemôžeš mat viacej ovocia na sklade ako 1000,
  • Nemôžeš ísť do mínusu.
  • Nemôžeš rezervovať viac ako je v obchode.

Dostupné online:
REXtester

Štruktúra DB - Diagram

audit_trigger_diagram

Vytvorenie DB a tabuliek

DB má dve tabuľky, v jednej je ponúkané ovocie, počet kusov na sklade a počet doteraz rezervovaných kusov ovocia, druhá tabuľka obsahuje informácie o kupujúcom.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE ovocie_na_predaj (
    meno text PRIMARY KEY,
    v_obchode integer NOT NULL,
    rezervovane integer NOT NULL DEFAULT 0,
    CHECK (v_obchode between 0 and 1000 ),
    CHECK (rezervovane <= v_obchode)
);
CREATE TABLE kupa_ovocia (
    id_kupcu serial PRIMARY KEY,
    meno_kupcu text,
    datum_ponuky timestamp default current_timestamp,
    druh_ovocia text REFERENCES ovocie_na_predaj,
    mnozstvo_ovocia integer
);

Funkcia

Funkcia pre automatizáciu rezervácií:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE FUNCTION rezervuj_tovar_po_ponuke()
  RETURNS TRIGGER
AS $$
BEGIN
  IF TG_OP = 'INSERT'
  THEN
    UPDATE ovocie_na_predaj
    SET rezervovane = rezervovane + NEW.mnozstvo_ovocia
    WHERE meno = NEW.druh_ovocia;
  ELSIF TG_OP = 'UPDATE'
    THEN
      UPDATE ovocie_na_predaj
      SET rezervovane = rezervovane - OLD.mnozstvo_ovocia + NEW.mnozstvo_ovocia
      WHERE meno = NEW.druh_ovocia;
  ELSIF TG_OP = 'DELETE'
    THEN
      UPDATE ovocie_na_predaj
      SET rezervovane = rezervovane - OLD.mnozstvo_ovocia
      WHERE meno = OLD.druh_ovocia;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Trigger

Trigger, ktorý zavolá funkciu po každej zmene, či pridaní záznamu do tabuľky kupa_ovocia.

1
2
3
CREATE TRIGGER sprava_rezervacii_obchodu
AFTER INSERT OR UPDATE OR DELETE ON kupa_ovocia
FOR EACH ROW EXECUTE PROCEDURE rezervuj_tovar_po_ponuke();

Test

Pridanie tovaru do obchodu:

1
2
INSERT INTO ovocie_na_predaj VALUES('JABLKO',500);
INSERT INTO ovocie_na_predaj VALUES('MELON',500);

Ponuka prvého kupca - 50 Jabĺk

1
2
3
INSERT INTO
kupa_ovocia(meno_kupcu,druh_ovocia,mnozstvo_ovocia)
VALUES('Rob','JABLKO',50);

Update ponuky kupca - zmena objednávky na 100 jabĺk

1
2
UPDATE kupa_ovocia SET mnozstvo_ovocia = 100
WHERE id_kupcu = 1;

Ponuka druhého kupca

1
2
3
INSERT INTO
kupa_ovocia(meno_kupcu,druh_ovocia,mnozstvo_ovocia)
VALUES('Ivan','JABLKO',400);

Výpis

1
2
SELECT * FROM ovocie_na_predaj;
SELECT * FROM kupa_ovocia;

Funkcia zároveň zabraňuje zmenu už rezervovaného ovocia.

1
2
3
4
UPDATE ovocie_na_predaj SET v_obchode = 100
WHERE meno = 'JABLKO';
Error(s), warning(s):
23514: new row for relation "ovocie_na_predaj" violates check constraint "ovocie_na_predaj_check"

A samozrejme kupec nemôže kúpiť viac ovocia než je na sklade.

1
2
3
4
UPDATE kupa_ovocia SET mnozstvo_ovocia = 500
WHERE id_kupcu = 1;
Error(s), warning(s):
23514: new row for relation "ovocie_na_predaj" violates check constraint "ovocie_na_predaj_check"

Pokiaľ ponuku zmažeme, zmaže sa aj rezervácia ovocia. V reálnych systémoch by sme mali túto ponuku archivovať ešte pred jej zmazaním.