I have two tables called sale and customer. I want to create a trigger that updates the column last_purchase on customer table on each new insert in the sale table.
Table customer: customer_id, name, last_sale, ...
Table sale: sale_id, customer_id, date, ...
CREATE TRIGGER update_last_sale BEFORE INSERT ON sale FOR EACH ROW EXECUTE...
I have started writing but I don't know how to do it.
Could someone help me?
CREATE FUNCTION update_customer_last_sale() RETURNS TRIGGER AS $$
BEGIN
UPDATE customer SET last_sale=now() WHERE cutomer_id=NEW.customer_id;
RETURN NEW;
END; $$
LANGUAGE plpgsql;
then
CREATE TRIGGER update_last_sale
BEFORE INSERT ON sale
FOR EACH ROW EXECUTE update_customer_last_sale;
NEW is the row which is about to be inserted in the sale table. (For an update row, it would be NEW for how the row will look after the update, and OLD for how the row looks before the update).
Basically, I don't think it is a good idea to store redundant data. The last_sale column in customers is just an aggregate of max(sales.sale_date).
It even gets worse if we use now() to touch customers.last_date. What would happen if we would need to re-insert some historical records (eg to recompute last year's taxes). That's what you get when you store redundant data....
-- modelled after Erwin's version
SET search_path='tmp';
-- DROP TABLE customers CASCADE;
CREATE TABLE customers
( id INTEGER NOT NULL PRIMARY KEY
, name VARCHAR
, last_sale DATE
);
-- DROP TABLE sales CASCADE;
CREATE TABLE sales
( id INTEGER NOT NULL PRIMARY KEY
, customer_id INTEGER REFERENCES customers(id)
, saledate DATE NOT NULL
);
CREATE OR REPLACE FUNCTION update_customer_last_sale() RETURNS TRIGGER AS $meat$
BEGIN
UPDATE customers cu
-- SET last_sale = now() WHERE id=NEW.customer_id
SET last_sale = (
SELECT MAX(saledate) FROM sales sa
WHERE sa.customer_id=cu.id
)
WHERE cu.id=NEW.customer_id
;
RETURN NEW;
END; $meat$
LANGUAGE plpgsql;
CREATE TRIGGER update_last_sale
AFTER INSERT ON sales
FOR EACH ROW
EXECUTE PROCEDURE update_customer_last_sale();
INSERT INTO customers(id,name,last_sale) VALUES(1, 'Dick', NULL),(2, 'Sue', NULL),(3, 'Bill', NULL);
INSERT INTO sales(id,customer_id,saledate) VALUES (1,1,'1900-01-01'),(2,1,'1950-01-01'),(3,2,'2011-12-15');
SELECT * FROM customers;
SELECT * FROM sales;
The results:
id | name | last_sale
----+------+------------
3 | Bill |
1 | Dick | 1950-01-01
2 | Sue | 2011-12-15
(3 rows)
id | customer_id | saledate
----+-------------+------------
1 | 1 | 1900-01-01
2 | 1 | 1950-01-01
3 | 2 | 2011-12-15
(3 rows)
I think you want the rule here.
CREATE RULE therule AS ON INSERT TO sale DO ALSO
(UPDATE customer SET customer.last_sale = now()
WHERE customer.customer_id=NEW.customer_id);
EDIT: but see the discussion in comments.
