MYSQL: How do I create a trigger that calculates a field value based on value from different table?

Question

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 would calculate the value of `PRICE` field, something like this: ``` price = (price_per_piece * count) + (price_per_piece * count * tableB.vat) ``` Is this possible? Thank you. Edit: And the row in table A has a foreign_key `dph_id` pointing to the corresponding row in table B. ``` CREATE TABLE `polozky` ( `id` int(11) NOT NULL AUTO_INCREMENT, `objednavka_id` int(11) NOT NULL, `dph_id` int(11) NOT NULL, `polozka` varchar(64) COLLATE utf8_unicode_ci NOT NULL, `cena` float NOT NULL, `mnozstvi` int(11) NOT NULL, `cena_za_kus` float NOT NULL, PRIMARY KEY (`id`), KEY `IdObjednavka` (`objednavka_id`,`dph_id`), KEY `objednavka_id` (`objednavka_id`), KEY `dph_id` (`dph_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=63663 ; ALTER TABLE `polozky` ADD CONSTRAINT `polozky_ibfk_7` FO…

Accepted Answer

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
$$
```

MYSQL: How do I create a trigger that calculates a field value based on value from different table?

Solution

Problem