MySQL中为product表创建价格更新时间记录触发器失败求助

Let's break down why your trigger is failing and fix it step by step:

The Problem with Your Original Trigger #

Your current code tries to run an UPDATE statement on the product table inside a BEFORE UPDATE trigger for the same table. This creates an infinite loop: the trigger fires when you update a row, then the UPDATE inside the trigger fires the trigger again, and so on until MySQL stops it with an error. Plus, this approach is unnecessary—BEFORE UPDATE triggers let you modify the row being updated directly without extra UPDATE calls.

The Corrected Trigger Code #

Here's the proper way to implement this logic:

DELIMITER //
CREATE TRIGGER update_price_change_time
BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
    -- Only update the time if the price actually changed
    IF NEW.price <> OLD.price THEN
        SET NEW.price_change_time = NOW();
    END IF;
END //
DELIMITER ;

Key Changes Explained #

• Removed the redundant UPDATE statement: Instead, we directly modify the NEW row object (which represents the row that's about to be updated). This is the standard way to adjust values in BEFORE triggers.
• Added an explicit IF condition: Makes the logic clearer, though technically the WHERE clause in your original code tried to do this—this is just more readable and efficient.
• Renamed the trigger: Gave it a more descriptive name (update_price_change_time) so you can easily identify its purpose later.

How to Run This in phpMyAdmin #

1. Navigate to your product table in phpMyAdmin.
2. Click the Triggers tab at the top.
3. Click Add Trigger.
4. Fill in the details:
   • Trigger Name: update_price_change_time
   • Trigger Time: BEFORE
   • Trigger Event: UPDATE
   • Table: product
   • Under "Trigger Definition", paste the code between BEGIN and END (you don't need to set DELIMITER here—phpMyAdmin handles that automatically for you).
5. Click Go to create the trigger.

Testing the Trigger #

Run an update query to test it out:

UPDATE product SET price = 99.99 WHERE id = 1; -- Replace with your product ID

Then check the price_change_time column for that row—it should show the current timestamp, but only if the price was actually different from the original value.

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