PostgreSQL触发器问题：如何仅更新Table_2关联的Table_1指定行

问题场景 #

现有两张表结构及数据如下：

Table_1

Table_2

需求是：当Table_2中某行的name字段更新时，仅更新Table_1中对应的关联记录。但原触发器代码会更新Table_1的所有关联记录，导致系统卡顿。

原触发器代码：

// Trigger Function Definition
CREATE OR REPLACE FUNCTION update_table_1_func()
RETURNS trigger AS
$$
BEGIN
    UPDATE table_1 SET name = table_2.name from table_2 WHERE table_1.id = table_2.fk_table_1_id;
    RETURN NULL;
END;
$$
LANGUAGE ‘plpgsql’;

// Trigger Definition
DROP TRIGGER IF EXISTS table_1_update_trigger_on_update ON “table_2”;
CREATE TRIGGER table_1_update_trigger_on_update
AFTER UPDATE ON “table_2”
EXECUTE PROCEDURE update_table_1_func();

问题分析 #

原触发器函数中的UPDATE语句直接关联整个table_2表，每次触发都会更新所有table_1中与table_2关联的记录，而非仅当前修改行对应的记录，这是导致系统负载过高的核心原因。

解决方案 #

1. 修改触发器函数 #

使用触发器内置的NEW变量获取当前更新行的信息，精准定位到对应的table_1记录：

CREATE OR REPLACE FUNCTION update_table_1_func()
RETURNS trigger AS
$$
BEGIN
    -- 仅更新当前修改行对应的Table_1记录
    UPDATE table_1 
    SET name = NEW.name 
    WHERE table_1.id = NEW.fk_table_1_id;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

2. 添加触发器触发条件（WHEN子句） #

通过WHEN子句过滤掉name字段未发生变化的更新操作，避免不必要的触发器执行：

DROP TRIGGER IF EXISTS table_1_update_trigger_on_update ON table_2;
CREATE TRIGGER table_1_update_trigger_on_update
AFTER UPDATE ON table_2
-- 仅当name字段确实发生变化时触发
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE PROCEDURE update_table_1_func();

关于WHEN OLD <> NEW的说明 #

• OLD和NEW是触发器的内置变量：OLD代表更新前的行数据，NEW代表更新后的行数据。
• 直接使用OLD.name <> NEW.name存在局限性：如果name字段可能为NULL，当新旧值一方为NULL另一方非NULL时，<> 会返回NULL，触发器不会触发。
• 更严谨的写法是OLD.name IS DISTINCT FROM NEW.name：它能正确处理NULL的情况，只要新旧name值不同（包括NULL差异），就会触发触发器。

内容的提问来源于stack exchange，提问作者Raky