MySQL同表行对比：新增产品时自动写入折扣表的触发器问题求助

原触发器的核心错误 #

1. AFTER INSERT触发器无OLD行：INSERT触发器中仅存在NEW对象（代表刚插入的记录），不存在OLD对象，所以WHERE NEW.product_id = OLD.product_id完全无效，这是最关键的错误。
2. INSERT语句语法错误：列名不能加括号，且缺少VALUES子句指定插入的具体值；discount的计算逻辑也有问题（原公式无法正确反映折扣幅度）。
3. 未正确获取同产品的最新历史价格：需要主动查询products表中当前产品的上一条最新记录，而非错误尝试用OLD对象获取。

修正后的触发器代码 #

CREATE DEFINER = CURRENT_USER TRIGGER `products_AFTER_INSERT` AFTER INSERT ON `products` FOR EACH ROW
BEGIN
    -- 声明变量存储历史最新价格和产品名称
    DECLARE last_price DECIMAL(10,2);
    DECLARE prod_name VARCHAR(255);
    
    -- 查询当前product_id的最新历史记录（排除刚插入的这条）
    SELECT price, name INTO last_price, prod_name
    FROM products
    WHERE product_id = NEW.product_id AND id < NEW.id
    ORDER BY id DESC
    LIMIT 1;
    
    -- 如果存在历史价格，且新价格低于历史价格，则插入折扣记录
    IF last_price IS NOT NULL AND NEW.price < last_price THEN
        INSERT INTO discount (product_id, name, old_price, new_price, discount)
        VALUES (
            NEW.product_id,
            prod_name,
            last_price,
            NEW.price,
            ROUND(((last_price - NEW.price)/last_price)*100, 2) -- 计算折扣率，保留2位小数
        );
    END IF;
END$$

关键说明 #

• 获取历史价格：通过id < NEW.id排除刚插入的记录，按id倒序取第一条，确保拿到该产品插入前的最新价格；如果用created_at字段判断最新，可将ORDER BY id DESC改为ORDER BY created_at DESC。
• 折扣率计算：采用标准公式((旧价-现价)/旧价)*100，得到百分比形式的折扣幅度，比如旧价100、现价80时，折扣率为20%。
• 空值判断：加入last_price IS NOT NULL，避免该产品首次插入（无历史记录）时触发错误。

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