pgsql trigger 01 - Ovocie, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

/* Vytvorenie novej tabulky 
   Zakladne podmienky(CHECK):
   Nemozes mat viacej ovocia na sklade ako 1000,
   nemozes ist do minusu,
   a nemozes rezervovat viac ako je v obchode
*/
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)
);

/*
    Tabulka uzivatelov ktory kupuju ovocie z obchodu
*/
CREATE TABLE kupa_ovocia (
id_kupcu serial PRIMARY KEY,
meno_kupcu text,
datum_ponuky timestamp default current_timestamp,
druh_ovocia text REFERENCES fruits_in_stock,
mnozstvo_ovocia integer
);

CREATE OR REPLACE FUNCTION reserve_stock_on_offer () RETURNS trigger
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE fruits_in_stock
SET reserved = reserved + NEW.offered_amount
WHERE name = NEW.fruit_name;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE fruits_in_stock
SET reserved = reserved - OLD.offered_amount
+ NEW.offered_amount
WHERE name = NEW.fruit_name;
ELSIF TG_OP = 'DELETE' THEN
UPDATE fruits_in_stock
SET reserved = reserved - OLD.offered_amount
WHERE name = OLD.fruit_name;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_reserve_stock_on_offer_change
AFTER INSERT OR UPDATE OR DELETE ON fruit_offer
FOR EACH ROW EXECUTE PROCEDURE reserve_stock_on_offer();

INSERT INTO fruits_in_stock VALUES('APPLE',500);
INSERT INTO fruits_in_stock VALUES('ORANGE',500);

/* kupec 1 */
INSERT INTO
fruit_offer(recipient_name,fruit_name,offered_amount
) VALUES('Bob','APPLE',100);

/* Update pre zmenu poctu zakupeneho ovocia */
UPDATE fruit_offer SET offered_amount = 100
WHERE offer_id = 1;

/*kupec 2*/
INSERT INTO
fruit_offer(recipient_name,fruit_name,offered_amount
) VALUES('Ivan','APPLE',100);

/* vypis */
SELECT * FROM fruits_in_stock;
SELECT * FROM fruit_offer;


absolute service time: 0,51 sec