[Question]: Best way to retain old field value after updating?

[medovanx]

I was trying to set up a way to capture previous price values whenever a product’s price changes, so I created a system using a price_history table combined with a before_price_update database trigger and an application-side event listener.

Database Trigger

First, I set up a price_history table to store historical records of key price fields (Retail_Price_Inc_Tax, Retail_Price_Ex_Tax, and Cost_Price_Ex_Tax). The table includes a timestamp and a historyCreated flag to track whether a price change has been fully processed.

I then added a before_price_update trigger to the main product table. This trigger fires before any update, checking if the old price values differ from the new ones. If there’s a change, it inserts or updates the price_history table with the previous values, capturing the state before the new prices are saved.

Event Listener

On the application side, I added an onPostUpdate listener to handle additional processing after the update completes. This listener retrieves any unprocessed entries from price_history (where historyCreated is 0), and if found, it creates a PriceChange object with the captured prices. This object is then added to the product’s price history in Pimcore, and the historyCreated flag is set to 1, marking the entry as fully processed.

Issue

This setup generally works well, but there's an issue on the first update of a product. When a product is updated for the first time, there’s no entry yet in price_history, so the original price isn't captured in the log. For example, if a product initially costs 1 and I change it to 2, I need to log that initial price of 1. Currently, however, the logging only kicks in on the second update (when I change from 2 to 3), and the initial price of 1 is lost.