Mysql: How Do I Create A Trigger That Calculates A Field Value Based On Value From Different Table?

In table A I have fields CENA(price), cena_za_kus(price_per_piece), mnozstvi(quantity). In table B I have field DPH(vat). I'd like to add a before update/insert trigger, which woul

Solution 1:

Try this:

DELIMITER $$

CREATE TRIGGER `yourDB_polozky`
   BEFORE INSERT
   ON `yourDB`.`polozky`
   FOR EACH ROW
BEGIN
   DECLARE vat_rate   float;

   SELECT DPH
     INTO dph_rate
     FROM `yourDB`.`dph`
    WHERE id = new.dph_id;

   SET new.cena = (1 + dph_rate) * (new.cena_za_kus * new.mnozstvi);
END
$$