mysql触发器需要一个参数_MYSQL：如何创建一个触发器，该触发器根据不同表中的值来计算字段值？...

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` FOREIGN KEY (`objednavka_id`) REFERENCES `objednavky` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `polozky_ibfk_8` FOREIGN KEY (`dph_id`) REFERENCES `dph` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

and

CREATE TABLE `dph` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`sekce` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'nastaveni',

`popis` varchar(8) COLLATE utf8_unicode_ci NOT NULL,

`DPH` int(11) unsigned NOT NULL,

`poradi` int(11) unsigned NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `DPH_2` (`DPH`),

UNIQUE KEY `popis` (`popis`),

KEY `DPH` (`DPH`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

解决方案

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

$$