update column with trigger in postgres

Question

I have this code for my table asf with column price and a column category:

CREATE OR REPLACE FUNCTION aft_update()
  RETURNS trigger AS
$$
BEGIN
UPDATE asf SET price = new.price='300' WHERE category = 'pro';
RETURN NEW;
END;

$$
LANGUAGE 'plpgsql';

CREATE TRIGGER updt_log
  AFTER UPDATE
  ON asf
  FOR EACH ROW
  EXECUTE PROCEDURE aft_update();

but for some reason even though the trigger runs successfully, the update does not work! I want to update multiple rows.

I already have a price filled in the column price (for example 400) and I want to change all prices under category pro to 300 — M. P.
– M. P., Commented Jun 25, 2021 at 21:34
Why do you want to change rows that are not affected by the UPDATE? — user330315
– user330315, Commented Jun 25, 2021 at 21:39
your query doesn't nake sense you have a trigger oin asf and write also a log for it in the same table? this should be another table — nbk
– nbk, Commented Jun 25, 2021 at 21:41
I want to change the price column, not the category one. I only use the category column to specify where I want the change to be — M. P.
– M. P., Commented Jun 25, 2021 at 21:41

nbk · Accepted Answer · 2021-06-25 21:44:19Z

1

For that you don't need any trigger or function at all

CREATE TABLE asf (id serial, price DECIMAL(10,2),category varchar(10))

INSERT INTO asf (price,category) VALUES (10.2,'pro'),(11.2,'pro')

UPDATE asf SET price = 300 where category = 'pro'

2 rows affected

SELECT * FROM asf

id |  price | category
-: | -----: | :-------
 1 | 300.00 | pro     
 2 | 300.00 | pro

db<>fiddle here

answered Jun 25, 2021 at 21:44

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M. P. Over a year ago

I know a trigger is not necessary but I was trying to use one and cant figure out why it's not functioning!

nbk Over a year ago

so you trigger the trigger after an update and the function updates which triggers the after update trigger and so on and so on

Collectives™ on Stack Overflow

update column with trigger in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related