Postgresql - How update another table after update one with specific data

Question

I have 2 tables,

CREATE TABLE B(
offer_id serial primary key,
number_of_placement int,
constraint num_of_placement_chk check (number_of_placement >0)
);

create table a (
id serial,
offer_id int references b(offer_id),//refer to table b offer_id
status varchar(20) default 'PENDING');

and my function and trigger,

Create or replace function update_no_offer_placement_func() 
returns trigger as $body$
begin
    if(new.status == 'ACCEPTED') then // if the status is update with 'ACCEPTED' value
        update b set number_of_placement = number_of_placement -1; //reduce the number of placement by 1
    end if;
end;
$body$ language plpgsql;

CREATE TRIGGER update_no_offer_placement_trig //after update table a
     AFTER update ON a
     FOR EACH ROW
     EXECUTE PROCEDURE update_no_offer_placement_func();

I would like to update table b's number of placement so tht it reduces its number by 1 if only the value updated in a is 'ACCEPTED'. How should i do that? I tried but its wrong.

Monty Khanna · Accepted Answer · 2015-12-11 10:35:58Z

1

Use This :

CREATE OR REPLACE FUNCTION update_no_offer_placement_func()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE B SET number_of_placement = number_of_placement -1 FROM a WHERE B.offer_id = a.offer_id AND a.status = 'ACCEPTED';
RETURN NULL;  -- AFTER trigger can return NULL
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER update_no_offer_placement_trig 
AFTER update ON a
FOR EACH ROW
EXECUTE PROCEDURE update_no_offer_placement_func();

answered Dec 11, 2015 at 10:35

Monty Khanna

1,1207 silver badges15 bronze badges

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

3 Comments

Wilz5363 Over a year ago

THANKS A LOT @Monty!!!! 1 MORE QUESTION, if wanna like after there's a acceptance, other status will turn 'REJECTED', do i jsut add UPDATE A SET STATUS = 'REJECTED' WHERE ID <> OLD.ID;??

Monty Khanna Over a year ago

do you want another trigger for this ? When this trigger will call ?

Wilz5363 Over a year ago

oh it's ok.. i just add few things and its done. this is what i added

IF NEW.status <> OLD.status THEN 		IF NEW.status = 'ACCEPTED' THEN 			UPDATE offer SET rem_num_count = rem_num_count - 1 FROM response WHERE response.offer_id = offer.offer_id AND response.status = 'ACCEPTED'; 			update response set status = 'REJECTED' WHERE student_id = old.student_id  			and offer_id <> old.offer_id; 		END IF; 	END IF;

Collectives™ on Stack Overflow

Postgresql - How update another table after update one with specific data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related