PostgreSQL触发器实现payment表status更新为true时自动设置end_date

问题原因 #

你编写的触发器存在以下几处错误：

• 关键字拼写错误：创建触发器的语句中，将UPDATE误写为UDATE，会直接触发语法报错
• 触发时机选择错误：使用AFTER UPDATE时机下，当前行的更新已经提交到表中，此时再执行UPDATE语句会再次触发该触发器，造成死循环；同时你写的UPDATE语句没有加过滤条件，会把全表的end_date都修改为当前日期
• 缺少业务逻辑判断：没有限定只有status字段被更新为true时才修改end_date，任意对payment表的行更新都会触发逻辑
• 触发器函数缺少必要返回值：PL/pgSQL编写的行级触发器函数必须返回TRIGGER类型值，BEFORE类触发器需要返回NEW对象才能让修改生效

正确实现方案 #

使用BEFORE UPDATE触发器，直接修改待写入的NEW行对象的end_date字段即可，不需要额外执行UPDATE语句：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_payment_end_date() RETURNS TRIGGER
AS $$
BEGIN
    -- 仅当status被更新为true时才修改end_date
    IF NEW.status = true AND OLD.status IS DISTINCT FROM NEW.status THEN
        NEW.end_date = CURRENT_DATE;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_update_payment_end_date 
BEFORE UPDATE ON payment
FOR EACH ROW 
EXECUTE FUNCTION update_payment_end_date();

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