PostgreSQL创建触发器在删除行之前。

Question

所以我有这两张表：

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


 -- Table addlisting
 columns: id, user_fk,price,date_added

这是我的问题：我想创建一个触发器，当我从addlisting表中删除一项目录时，该目录的价格将添加到user表中的"earnedmoney"列中。

有人能帮助我吗？谢谢！

- Nano

Answer 1

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触发器，这不会有任何区别。

- Laurenz Albe

---

谢谢，你很棒！ - Nano

@mazunki 不好意思，麻煩您發一個新問題且提供完整的信息。 - Laurenz Albe

Answer 2

只需创建常规的BEFORE DELETE触发器：

test1=# create table addlisting (id serial PRIMARY KEY, user_fk integer, price float, date_entered date);
CREATE TABLE

test1=# insert into users (name, sername, password, token) values ('user1', '', '123', '123'), ('user2', '', '234', '234');
INSERT 0 2

test1=# insert into addlisting (user_fk, price, date_entered) values (1, 100, now()), (1, 34, now()), (2, 465, now());
INSERT 0 3

test1=# select * from users;
 id | name  | sername | password | token | earnedmoney 
----+-------+---------+----------+-------+-------------
  1 | user1 |         | 123      | 123   |            
  2 | user2 |         | 234      | 234   |            
(2 rows)

test1=# select * from addlisting;
 id | user_fk | price | date_entered 
----+---------+-------+--------------
  1 |       1 |   100 | 2016-07-08
  2 |       1 |    34 | 2016-07-08
  3 |       2 |   465 | 2016-07-08
(3 rows)

test1=# CREATE OR REPLACE FUNCTION update_price() RETURNS trigger AS $emp_stamp$
test1$#     BEGIN
test1$#         update users 
test1$#         set earnedmoney = coalesce(earnedmoney, 0) + coalesce(OLD.price, 0)
test1$#         where id = OLD.user_fk;
test1$# 
test1$#         return OLD;
test1$#     END;
test1$# $emp_stamp$ LANGUAGE plpgsql;
CREATE FUNCTION

test1=# CREATE TRIGGER on_delete_addlisting
test1-# BEFORE DELETE ON addlisting
test1-#     FOR EACH ROW EXECUTE PROCEDURE update_price();
CREATE TRIGGER

测试：

test1=# delete from addlisting where id in (1,3);                    
DELETE 2

test1=# select * from addlisting;

 id | user_fk | price | date_entered 
----+---------+-------+--------------
  2 |       1 |    34 | 2016-07-08
(1 row)

test1=# select *from users;            
 id | name  | sername | password | token | earnedmoney 
----+-------+---------+----------+-------+-------------
  1 | user1 |         | 123      | 123   |         100
  2 | user2 |         | 234      | 234   |         465
(2 rows)


test1=# delete from addlisting;
DELETE 1

test1=# select *from users;
 id | name  | sername | password | token | earnedmoney 
----+-------+---------+----------+-------+-------------
  2 | user2 |         | 234      | 234   |         465
  1 | user1 |         | 123      | 123   |         134
(2 rows)

- Ivan Burlutskiy

---

非常详细，我很感激。顺便问一下，coalesce() 函数是做什么的？ - Nano

如果第一个参数为空，则返回第二个参数。coalesce（arg1，arg2）= case when arg1 is null then arg2 else arg1 end - Ivan Burlutskiy