PostgreSQL新增service表记录时自动生成payment记录及触发器报错修复

原有代码的错误点 #

• 条件判断语法错误：IF NEW service THEN为无效语法，我们可以直接将触发器绑定到INSERT事件，无需额外判断操作类型
• 外键取值错误：插入payment时应该取新插入的service记录主键NEW.id，而非service.id
• 缺少必要的函数声明：未指定PL/pgSQL语言，也没有补全触发器返回值与函数闭合标识
• 未创建对应触发器绑定到service表：函数写完后需要单独创建触发器关联表操作事件

完整可运行实现代码 #

第一步：修正并创建触发器函数 #

CREATE OR REPLACE FUNCTION insert_payment() 
RETURNS TRIGGER AS $insert_payment$
BEGIN
    INSERT INTO payment(fk_service, status) 
    VALUES (NEW.id, false);
    RETURN NULL;
END;
$insert_payment$ LANGUAGE plpgsql;

第二步：创建绑定到service表的INSERT触发器 #

CREATE TRIGGER trigger_after_service_insert
AFTER INSERT ON service
FOR EACH ROW
EXECUTE FUNCTION insert_payment();

补充说明 #

• 若还未正式创建payment表，建议将fk_service的字段类型从BIGSERIAL改为BIGINT，BIGSERIAL是自增主键专用类型，外键不需要自增属性，可避免后续出现非预期问题
• 触发器设置为AFTER INSERT可以保证service记录已经成功写入数据库再创建关联payment记录，避免事务回滚时产生脏数据

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