postgresql - Postgresql 13 - 为组记录触发一次

Question

我有表：

| id |depth| depth_ex| b_id|
|----|-----|---------|-----|
|  1 |   0 |   0.00  |  1  |
|  2 |   1 |   1.00  |  1  |
|  3 |   2 |   3.00  |  1  |
| 15 |   3 |   6.00  |  1  |
|  6 |  35 |  41.00  |  1  |
| 12 | 100 | 141.00  |  1  |
|  9 |   0 |   0.00  |  2  |
| 10 |  20 |  20.00  |  2  |
| 11 |  40 |  60.00  |  2  |

对于这个表，我创建了 TRIGGER：

CREATE OR REPLACE FUNCTION depth_ex() RETURNS trigger
   LANGUAGE plpgsql AS
$$
BEGIN
    RAISE NOTICE '!!!!!!!!!!!!!!'; 

    UPDATE a
      SET depth_ex = sub.new_depth
    FROM (
        SELECT
             id,
            SUM(depth) OVER (ORDER BY depth ASC) AS new_depth
        FROM
            a
        WHERE b_id = NEW.b_id
    ) sub
    WHERE a.id = sub.id;
 
    RETURN NULL;
END;
$$;
 
CREATE CONSTRAINT TRIGGER depth_ex_trig
   AFTER INSERT OR UPDATE OF depth ON a
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE depth_ex();

后：

BEGIN;
UPDATE a SET depth = 6 where id = 2;
UPDATE a SET depth = 10 where id = 3;
UPDATE a SET depth = 15 where id = 15;
COMMIT;

我期待看到！！！！！！！只有一个，但我看了三遍。

NOTICE:  !!!!!!!!
NOTICE:  !!!!!!!!
NOTICE:  !!!!!!!!
COMMIT

Query returned successfully in 31 msec.

如何改写程序或触发器看 NOTICE: !!!!!!!!! 只有一次，还是触发一组记录（具有相同的 b_id）？

问候

标签： postgresql