SQL触发器重复生成记录求助：插入支付数据时审计表多新增行

问题根源排查 #

从你提供的输出结果来看，三条记录中仅第一条包含changetype和time_stamp字段，后两条缺失，说明你大概率在payment表上绑定了多个触发器，或是之前创建过的旧触发器未删除，导致每次插入操作被多次触发。

解决步骤 #

1. 检查当前绑定的触发器
   执行以下SQL查询，查看payment表上所有触发器：

SELECT tgname, tgtype FROM pg_trigger WHERE tgrelid = 'payment'::regclass;

如果结果显示多个针对INSERT事件的触发器，这就是重复生成记录的直接原因。

2. 清理重复/旧触发器
   删除现有触发器和关联函数（若存在旧版本）：

-- 删除触发器
DROP TRIGGER IF EXISTS audit_table ON payment;
-- 删除触发器函数
DROP FUNCTION IF EXISTS audit();

3. 重新创建正确的触发器和函数
   优化后的触发器函数（用VALUES替代SELECT，逻辑更简洁）：

CREATE FUNCTION audit()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
    AS
$$
BEGIN                        
    INSERT INTO payment_history (
        memberid, 
        paymentid, 
        amountpaid_new, 
        amountpaiddate_new, 
        amountpaiduntildate_new, 
        changetype, 
        time_stamp
    )
    VALUES (
        NEW.memberid, 
        NEW.paymentid, 
        NEW.amountpaid, 
        NEW.amountpaiddate, 
        NEW.amountpaiduntildate, 
        'I', 
        CURRENT_TIMESTAMP
    );
    RETURN NEW;
END;
$$

注：INSERT操作没有旧数据，因此填充xxx_new字段、留空旧字段，更符合审计表的设计逻辑。

再创建触发器：

CREATE TRIGGER audit_table
BEFORE INSERT ON payment
FOR EACH ROW
EXECUTE PROCEDURE audit(); 

4. 测试插入操作
   再次执行你的插入语句，检查payment_history表，此时应仅生成一条审计记录。

额外优化建议 #

• 审计表中amountpaid字段用varchar(300)存储金额不合理，建议改用numeric或decimal类型，避免精度丢失和格式问题。
• 可给触发器函数添加注释，明确其作用，方便后续维护。

内容的提问来源于stack exchange，提问作者Jean-Paul Azzopardi