PostgreSQL触发器更新同一表中的所有行

Question

我想在更新一个或两个字段时计算一个字段，但触发器计算所有表的行具有相同的值，而不仅仅是行更新。怎么了?谢谢你。

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

输入图片描述

Answer 1

不要使用UPDATE，将计算值赋给BEFORE触发器中的新记录:

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

要做到这一点，你需要一个BEFORE触发:

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