我试图用一个函数编写一个触发器来更新一个表中某列的值，同时使用三个表的联接执行搜索。
我想做的是，当成员变为活动成员时，增加成员所在的每个组中活动成员的数量。因此，如果一个成员在组a和组b中，当它们处于活动状态时，组a和组b的numactivemembers将各自增加1。

Table member
name
church
state (0 is inactive, 1 is active)

Table churchgrp
church
grp
numactivemembers

Table memberchurchgrp
name
church
grp

通过此选择可以获得正确的行：

SELECT cg.church, cg.grp, cg.numactivemembers FROM churchgrp cg inner join memberchurchgrp ucg ON cg.church = ucg.church AND cg.grp = ucg.grp inner join member u ON m.name = ucg.name AND m.church = ucg.church AND ucg.grp = cg.grp WHERE m.name = 'John Doe' AND m.state = 1;

我的触发器正在更新成员的状态。

CREATE TRIGGER inc_grpnumactivemembers_tgr
  AFTER UPDATE ON member
  FOR EACH ROW
    WHEN OLD.state = 0 AND NEW.state = 1
    EXECUTE PROCEDURE incgrpnumactivemembers();

不起作用的是函数中的更新。
这将执行，但会增加组中的所有行，而不仅仅是成员所在的组，这是不正确的。

CREATE OR REPLACE FUNCTION incgrpnumactivemembers()
RETURNS trigger AS
$$
BEGIN
UPDATE churchgrp SET numactivemembers = num_active_members + 1 FROM (SELECT cg.church, cg.grp, cg.numactivemembers FROM churchgrp cg inner join memberchurchgrp ucg ON cg.church = ucg.church AND cg.grp = ucg.grp inner join member u ON m.name = ucg.name AND m.church = ucg.church AND ucg.grp = cg.grp WHERE m.name = NEW.name AND m.state = 1)
AS sq
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

这个更新给了我这个错误：错误：表名“cg”指定了多次

UPDATE churchgrp as cg SET numactivemembers = num_active_members + 1 FROM churchgrp cg inner join memberchurchgrp ucg ON cg.church = ucg.church AND cg.grp = ucg.grp inner join member u ON m.name = ucg.name AND m.church = ucg.church AND ucg.grp = cg.grp WHERE m.name = NEW.name AND m.state = 1)

这个更新给了我这个错误：错误：列引用“numactivemembers”不明确第1行：更新组set numactivemembers=numactivem。。。

UPDATE churchgrp SET numactivemembers = num_active_members + 1 FROM churchgrp cg inner join memberchurchgrp ucg ON cg.church = ucg.church AND cg.grp = ucg.grp inner join member u ON m.name = ucg.name AND m.church = ucg.church AND ucg.grp = cg.grp WHERE m.name = NEW.name AND m.state = 1;

感谢您的帮助。