Trigger for update column i´m trying to create trigger for update column, resulting of the min value of other columns. My sql code it´s the following:

DELIMITER //
CREATE TRIGGER t__price_final AFTER UPDATE ON market__combination_price
FOR EACH ROW
BEGIN
CASE 
WHEN market__combination_price.price &lt;= market__combination_price.offer 
THEN UPDATE market__combination_price SET price_final = 
market__combination_price.price;
WHEN market__combination_price.price &gt; market__combination_price.offer 
THEN UPDATE market__combination_price SET price_final = 
market__combination_price.offer;
END CASE;
END;
//


But it doesn´t work. What could be the problem?
Several things are wrong:


While the UPDATE statement can be inside a CASE statement, you don't really need it. You only want to update a single row. And you have access to that row with the OLD and NEW keywords (values before and after the update. You can use SET NEW.price = ...
To modify a value in an update trigger, it has to be an BEFORE trigger, not AFTER.
The CASE statement (that has a SET) can be converted to a CASE expression (inside a SET)
Finally, if the columns are not nullable, it would be simpler to use the function LEAST() instead of a CASE expression.


The trigger code:

DELIMITER //
CREATE TRIGGER t__price_final 
  BEFORE UPDATE ON market__combination_price
  FOR EACH ROW
  BEGIN
    SET NEW.price_final = LEAST(NEW.price, NEW.offer) ;
                     -- if price or offer are nullable:
                     -- = CASE WHEN offer &lt; price OR price IS NULL
                     --       THEN offer
                     --       ELSE price
                     --   END ;
  END ;
//