CODEWITHSUNDEEP
sqlpostgresqltriggersupdates
Leonardo Carvallo
Leonardo Carvallo

Reputation: 11

PostgreSQL Trigger update all row in the same table

I want calculate a field when update one or two fields but Trigger calculate all table's row with same value, not only the row updated. What is wrong ..? Thank you.

Create or Replace Function hospital_ocupation() returns Trigger
as
$$
Begin

update hospital set p_ocupation = (new.n_cases*100)/new.capacity
where old.n_case <> new.n_case; 

return new;

End
$$
language plpgsql;


Create Trigger update_ocupation after update on hospital
for each row
WHEN (pg_trigger_depth() = 0)
execute procedure hospital_ocupation();


update hospital set capacity=500,n_cases=50
where id_hospital = 1

enter image description here

Upvotes: 1

Views: 773

Answers (1)

user330315
user330315

Reputation:

Don't use UPDATE, assign the calculated value to the new record in a BEFORE trigger:

create or replace function hospital_ocupation() 
  returns trigger
as
$$
begin
  new.p_ocupation := (new.n_cases*100)/new.capacity;
  return new;
End
$$
language plpgsql;

To make that work you need a BEFORE trigger:

create trigger update_ocupation 
BEFORE update on hospital
for each row
WHEN (old.n_case <> new.n_case) 
execute procedure hospital_ocupation();

Upvotes: 1

Related Questions