触发更新Postgres 9中的当前日期 [英] Trigger to update current date in Postgres 9

问题描述

我有两个表 sale 和 customer 。我想创建一个触发器，更新<$ c $中每个新插入的客户表上的 last_purchase c> sale 表。

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.

表客户：customer_id，name，last_sale，...

表销售：sale_id，customer_id，date，...

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;

然后

CREATE TRIGGER update_last_sale
BEFORE INSERT ON sale
FOR EACH ROW EXECUTE update_customer_last_sale;

NEW 插入销售表。 （对于更新行，将会是 NEW 用于更新后行的样式， OLD 该行在更新之前）。

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).

这篇关于触发更新Postgres 9中的当前日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！