PostgreSQL在删除行之前创建触发器 [英] Postgresql Create Trigger Before Deleting A Row

问题描述

所以我有这两个表:

 Table user
 columns: id,name,surname, password,token,earnedmoney


 Table addlisting
 columns: id, user_fk,price,date_added

这是我的问题:我想创建一个触发器，以便当我从表addlisting中删除列表时，列表的价格会添加到表用户中的"earnedmoney"列中.

Here is my problem: I would like to create a trigger so that when I delete a listing from the table addlisting, the price of the listing gets added to the column "earnedmoney" which is in the table user.

有人可以帮我吗?谢谢！

Could somebody help me? Thank you!

推荐答案

CREATE OR REPLACE FUNCTION add_money() RETURNS trigger AS
$$BEGIN
   UPDATE "user" SET earnedmoney = earnedmoney + OLD.price
      WHERE id = OLD.user_fk;
   RETURN OLD;
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER add_money
   BEFORE DELETE ON addlisting FOR EACH ROW
   EXECUTE PROCEDURE add_money();

它也可以是AFTER触发器，不会有任何作用.

It could also be an AFTER trigger, that would make no difference.

这篇关于PostgreSQL在删除行之前创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！