From 98dda4beafd082b96c6dc8b3f44e1589a39069c1 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Fri, 29 Jun 2018 21:29:16 +0200 Subject: sql update --- data/sql/trigger.sql | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) create mode 100644 data/sql/trigger.sql (limited to 'data/sql/trigger.sql') diff --git a/data/sql/trigger.sql b/data/sql/trigger.sql new file mode 100644 index 0000000..d26a39d --- /dev/null +++ b/data/sql/trigger.sql @@ -0,0 +1,27 @@ +BEGIN TRANSACTION; +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_insert AFTER INSERT ON restock BEGIN + UPDATE products SET amount = products.amount + NEW.amount WHERE products.id = NEW.product; +END; + +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_delete AFTER DELETE ON restock BEGIN + UPDATE products SET amount = products.amount - OLD.amount WHERE products.id = OLD.product; +END; + +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_update AFTER UPDATE ON restock BEGIN + UPDATE products SET amount = products.amount - OLD.amount WHERE products.id = OLD.product; + UPDATE products SET amount = products.amount + NEW.amount WHERE products.id = NEW.product; +END; + +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_insert AFTER INSERT ON sales BEGIN + UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product; +END; + +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_delete AFTER DELETE ON sales BEGIN + UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product; +END; + +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_update AFTER UPDATE ON sales BEGIN + UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product; + UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product; +END; +COMMIT; -- cgit v1.2.3