Postgresql Create Trigger Before Deleting A Row

postgresql triggers plpgsql stored-functions
Nano picture Nano · Jul 8, 2016 · Viewed 15.6k times · Source

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!

Answer

Laurenz Albe picture Laurenz Albe · Jul 8, 2016 
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.

Related questions

Postgresql, update if row with some unique value exists, else insert

I have a URLs table. They contain (id int primary key, url character varying unique, content character varying, last analyzed date). I want to create trigger or something(rule may be), so each time i make insert from my java …

Read More
ERROR: unterminated quoted string at or near

While executing below shown trigger code using ANT I am getting the error org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer" Position: 57 I am able to sucessfully execute the below code through PGADmin (…

Read More
Postgres trigger after insert accessing NEW

I have a pretty simple trigger: CREATE OR REPLACE FUNCTION f_log_datei() RETURNS TRIGGER AS $$ BEGIN INSERT INTO logs (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id); END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER log_datei AFTER INSERT …

Read More