PostgreSQL触发器IF EXISTS失效，当前用户及日期插入失败求助

问题根源分析 #

你的触发器存在多个逻辑错误，导致仅执行else分支、用户/日期字段无法更新：

1. 触发器时机与NEW/OLD对象使用错误

   • 定义为AFTER触发器时，原表的INSERT/UPDATE操作已完成，此时修改NEW字段不会同步到原表；DELETE操作触发时，NEW对象为空，只能用OLD获取被删除的行数据。
   • 你的DELETE分支用new.city_id必然得到NULL，导致city_deleted插入失败，同时exists判断中new.country_name为NULL，条件不成立直接进入else分支。

2. INSERT分支逻辑错误
   INSERT分支直接向city表插入新行（仅含crt_usr_id和crt_date），而非更新刚插入的那条记录，完全不符合需求。

3. 返回值错误
   若使用BEFORE触发器（修正时机后），必须返回NEW或OLD，否则原操作会被取消；你的代码返回NULL会导致INSERT/UPDATE操作失效。

修正后的代码 #

触发器函数

create or replace function sp_city_dt_trg() returns trigger as $$
declare
    target_country_name varchar;
begin
    -- 根据操作类型选择对应的数据对象获取国家名称
    if tg_op = 'DELETE' then
        target_country_name := old.country_name;
    else
        target_country_name := new.country_name;
    end if;

    if exists (select 1 from country_master where country_name = target_country_name) then
        case tg_op
            when 'INSERT' then
                -- 直接给新行的创建字段赋值
                new.crt_usr_id := current_user;
                new.crt_date := current_date;
            when 'UPDATE' then
                -- 更新修改字段，同时写入历史表
                new.upt_usr_id := current_user;
                new.upt_date := current_date;
                insert into city_history (city_name, state_name, country_name, change_date) 
                values (old.city_name, old.state_name, old.country_name, current_date);
            when 'DELETE' then
                -- 用OLD获取被删除的数据插入到删除记录表
                insert into city_deleted select * from city where city_id = old.city_id;
        end case;
    else
        raise notice 'No such country: %', target_country_name;
        -- 如果需要阻止非法操作，可替换为抛出异常：raise exception 'No such country: %', target_country_name;
    end if;

    -- 根据操作类型返回对应对象，确保原操作生效
    return case tg_op when 'DELETE' then old else new end;
end;
$$ language plpgsql;

触发器创建语句

create or replace trigger trg_city
before insert or update or delete on city
for each row
execute function sp_city_dt_trg();

关键修改说明 #

• 将触发器改为BEFORE类型，确保修改NEW字段能同步到原表。
• DELETE操作中使用OLD对象获取被删除的行数据，修复exists判断和city_deleted插入逻辑。
• INSERT分支直接赋值给NEW的crt_usr_id和crt_date，而非插入新行。
• 统一处理target_country_name，确保所有操作类型下都能正确获取国家名称进行判断。
• 修正返回值，确保原INSERT/UPDATE/DELETE操作能正常执行。

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