Postgresql 在删除一行之前创建触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38268715/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql Create Trigger Before Deleting A Row
提问by Nano
so i have these two tables:
所以我有这两个表：
Table user
columns: id,name,surname, password,token,earnedmoney
Table addlisting
columns: id, user_fk,price,date_added
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.
这是我的问题：我想创建一个触发器，以便当我从表 addlisting 中删除列表时，列表的价格会添加到表 user 中的“earnedmoney”列中。
Could somebody help me? Thank you!
有人可以帮助我吗？谢谢！
回答by Laurenz Albe
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();
It could also be an AFTERtrigger, that would make no difference.
它也可能是一个AFTER触发器，没有任何区别。
回答by Ivan Burlutskiy
Just create regular BEFORE DELETE trigger:
只需创建常规 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
Test:
测试：
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)
