本文介绍了MYSQL:如何创建一个触发器,该触发器根据不同表中的值来计算字段值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在表A 中,我有字段 CENA (价格), cena_za_kus (price_per_piece ), mnozstvi (数量)。 在表B 中,我有字段 DPH (vat)。
In table A I have fields CENA(price), cena_za_kus(price_per_piece), mnozstvi(quantity). In table B I have field DPH(vat).
我想添加一个更新/插入之前触发器,该触发器将计算 PRICE 字段,如下所示:
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.
编辑:表 A中的行具有一个外键 dph_id 指向表B 中的相应行。
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;和
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 ;推荐答案
尝试一下:
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:如何创建一个触发器,该触发器根据不同表中的值来计算字段值?
发布评论