Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
pgsql trigger 01 - Ovocie
--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;
run
|
edit
|
history
|
help
0
univesp
Social Network SQL
MovieRating SQL
Engenheiro
Assignment 1(SET A)
SQL Moving Rating Practice by Han Wang 20200720
Black Wall
FlujoPadre
Returning IDs with exactly 3 consecutive months of invoice_amt >2000
Select the latest id from last week from each different contact without repeating barcodes.....