更新多个其他表后的mysql触发器

Question

我有两张 table

“description_acc_all_lang” - 这包含 15 种语言的文本翻译，每个字段都是语言翻译

CREATE TABLE `description_acc_all_langs` (<br>
  `code` varchar(4) NOT NULL,(<br>
  `en_desc` varchar(256) NOT NULL,<br>
  `fr_desc` varchar(256) NOT NULL,<br>
  `es_desc` varchar(256) NOT NULL,<br>
  `pt_desc` varchar(256) NOT NULL,<br>
  `da_desc` varchar(256) NOT NULL,<br>
  `de_desc` varchar(256) NOT NULL,<br>
  `nl_desc` varchar(256) NOT NULL,<br>
  `no_desc` varchar(256) NOT NULL,<br>
  `sv_desc` varchar(256) NOT NULL,<br>
  `fi_desc` varchar(256) NOT NULL,<br>
  `pl_desc` varchar(256) NOT NULL,<br>
  `it_desc` varchar(256) NOT NULL,<br>
  `ru_desc` varchar(256) NOT NULL,<br>
  `cs_desc` varchar(256) NOT NULL,<br>
  `hu_desc` varchar(256) NOT NULL,<br>
  PRIMARY KEY  (`code`)<br>
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

此表中的一条记录

+------+-------------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+<br>
| code | en_desc                             | fr_desc | es_desc | pt_desc | da_desc | de_desc | nl_desc | no_desc | sv_desc | fi_desc | pl_desc | it_desc | ru_desc | cs_desc | hu_desc |<br>
+------+-------------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+<br>
| hs   | 30 minutes drive to big supermarket |         |         |         |         |         |         |         |         |         |         |         |         |         |         |<br>
+------+-------------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

代码 - 最多 4 个字母的唯一代码
en_desc - 是英文文本
fr_desc - 是法语文本
es_desc - 是西类牙语文本
.._desc - 是......文本
等等

第二个表包含多条记录 - 15 种语言描述中的每一种记录

code - 4 letter code this is <br>
desc - Text string - this needs to be updated from the xx_desc field in the "description_acc_all_langs" table lang - 2 letter country code.



CREATE TABLE `description_acc` (<br>
  `acc_id` int(11) NOT NULL auto_increment,<br>
  `code` varchar(10) collate utf8_unicode_ci default NULL,<br>
  `desc` varchar(255) collate utf8_unicode_ci default NULL,<br>
  `lang` varchar(2) collate utf8_unicode_ci NOT NULL,<br>
  PRIMARY KEY  (`acc_id`),<br>
  UNIQUE KEY `codelang` (`code`,`lang`)<br>
) ENGINE=MyISAM AUTO_INCREMENT=211 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

从显示所有语言的代码“hs”的所有记录的表格中提取

+--------+------+-----------------------------------------------------------------------+------+
| acc_id | code | desc                                                                  | lang |<br>
+--------+------+-----------------------------------------------------------------------+------+<br>
|     25 | hs   | 30 minut jízdy do velkých supermarketů                             | cs   |<br>
|     18 | hs   | 30 minutters kørsel til stort supermarked                            | da   |<br>
|     26 | hs   | 30 Minuten Fahrt zum Supermarkt                                       | de   |<br>
|     14 | hs   | 30 minutes drive to big supermarket                                   | en   |<br>
|     28 | hs   | 30 minutos en coche de grandes supermercados                          | es   |<br>
|     21 | hs   | 30 minuutin ajomatkan suuri supermarket                               | fi   |<br>
|     27 | hs   | 30 minutes en voiture au grand supermarché                           | fr   |<br>
|     23 | hs   | 30 perc autóútra a nagy szupermarket                                | hu   |<br>
|     17 | hs   | 30 minuti di auto a grande supermercato                               | it   |<br>
|     15 | hs   | 30 minuten rijden naar grote supermarkt                               | nl   |<br>
|     19 | hs   | 30 minutters kjøretur til store supermarked                          | no   |<br>
|     24 | hs   | 30 minut jazdy na duży supermarket                                   | pl   |<br>
|     16 | hs   | 30 minutos de carro de um supermercado grande                         | pt   |<br>
|     22 | hs   | 30 минÑÑ ÐµÐ·Ð´Ñ Ð´Ð¾ болÑÑого ÑÑпеÑмаÑкеÑа           | ru   |<br>
|     20 | hs   | 30 minuters bilfärd till stora snabbköp                             | sv   |<br>
+--------+------+-----------------------------------------------------------------------+------+<br>
15 rows in set (0.00 sec)<br>

如果在“area_description_acc_all_langs”中更新了单个字段，我的触发器需要更新每个“area_description_acc”语言记录中的记录

因此，实际上“area_description_acc_all_langs”在单个记录中包含所有翻译和 1 个代码标识符，“area_description_acc”包含每个代码的 15 种语言中的每一种的 15 个单独记录，并带有一个字段“code”来识别它和一个语言标识符字段“郎”

我开始尝试理解触发器是如何构造的，但我只是把自己束缚在了结中

delimiter //
Create trigger update_all_recs
after update on description_acc_all_langs
for each row
begin
  update area_description_acc
  set desc = new.en_desc
  where members_house_area_description_acc_all_langs.code=members_house_area_description_acc.code;
    end//

delimiter ;

任何关于如何构造触发器的帮助都会帮助我理解，我已经切碎了很多例子，但是在 2 天和喝了太多咖啡之后，我认为我需要指导，也许还需要一些治疗。问候。

Answer 1

你在找这样的东西吗？

DELIMITER $$
CREATE TRIGGER tg_update_all_recs
AFTER UPDATE ON description_acc_all_langs
FOR EACH ROW
BEGIN
    UPDATE description_acc
       SET `desc` = NEW.en_desc
     WHERE code = NEW.code AND lang = 'en';
    UPDATE description_acc
       SET `desc` = NEW.fr_desc
     WHERE code = NEW.code AND lang = 'fr';
    UPDATE description_acc
       SET `desc` = NEW.es_desc
     WHERE code = NEW.code AND lang = 'es';
    UPDATE description_acc
       SET `desc` = NEW.pt_desc
     WHERE code = NEW.code AND lang = 'pt';
   -- continue for all other languages
END $$
DELIMITER ;