Postgresql Create Trigger Before Deleting A Row

Question

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.

Could somebody help me? Thank you!

Solution 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();

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

Solution 2

Just create regular BEFORE DELETE trigger:

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)

Comments

[Nano]

Thanks, you are awesome!

[Nano]

That is very detailed, I appreciate that. Btw, what does the coalesce() function do?

[Ivan Burlutskiy]

If first argument is null then it will return second. coalesce(arg1, arg2) = case when arg1 is null then arg2 else arg1 end

[Laurenz Albe]

@mazunki No; please ask a new question with complete information.