BWDHRBsp

1CREATE OR REPLACE FUNCTION update_totals() RETURNS void AS $$
2    UPDATE orders SET
3       total_fees = (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id),
4       total_profit = (CASE WHEN total_cost IS NOT NULL THEN total - total_tax - total_cost - (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id) ELSE NULL END)
5    WHERE id = OLD.order_id;
6$$
7LANGUAGE SQL;
8
9CREATE TRIGGER update_totals AFTER INSERT 
10OR UPDATE OF fees
11ON order_items INITIALLY DEFERRED
12FOR EACH ROW
13WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();
14	
15missing FROM-clause for table "old"
16	
17CREATE TRIGGER update_totals AFTER INSERT 
18OR UPDATE OF fees
19ON order_items
20REFERENCING OLD ROW AS old_order
21DEFERRABLE INITIALLY DEFERRED
22FOR EACH ROW
23WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();
24	
25CREATE TABLE IF NOT EXISTS orders(ID INT NOT NULL PRIMARY KEY, X INT);
26CREATE TABLE IF NOT EXISTS order_items (ID INT NOT NULL PRIMARY KEY, ORDER_ID INT, Y INT); 
27
28INSERT INTO orders VALUES(1, 0);
29INSERT INTO order_items VALUES(10, 1, 20);
30INSERT INTO order_items VALUES(11, 1, 30);
31
32CREATE OR REPLACE FUNCTION func_order_items() RETURNS trigger AS 
33$$
34BEGIN
35  IF (TG_OP = 'UPDATE') THEN
36    UPDATE orders
37    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = OLD.order_id)
38    WHERE ID = OLD.order_id;
39  ELSIF (TG_OP = 'INSERT') THEN
40    UPDATE orders
41    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = NEW.order_id)
42    WHERE ID = NEW.order_id;
43  END IF;
44  RETURN NEW;
45END
46$$
47LANGUAGE PLPGSQL;
48
49CREATE TRIGGER trigger_order_items 
50AFTER INSERT OR UPDATE 
51ON order_items 
52FOR EACH ROW EXECUTE PROCEDURE func_order_items();
53
54UPDATE order_items SET Y = 200 WHERE ID = 10;
55INSERT INTO order_items VALUES (12, 1, 200);