用于透视表的Mysql触发器

本文介绍了用于透视表的Mysql触发器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

故事是我有两个系统从两个表中获取数据。由于缺乏适当的DB结构。 这是CodeIgniter管理页面的第一个表格。

╔══ ════════════════════════════════b $ b║ID║产品名称║产品价值║╠═ ═════════════════════════════════b $ b║1║A║10║║ 2║B║20║║3║C║30║ ... ║26║Z║260║╚══════════════════════════════════════════════════════════════════════ ════════════════════════════════════════════════════════════════════════════════════════是另一个系统的表。这是系统适应的结构：

╔═════════════════ ═════════════════╗b$ b║A║B║C║D║......║Z║╠═════╬ ══════════════════════════════b $ b║10║20║30║40║......║ 260║╚═════════════════════════════════════════════════════════════════ br $> b $ b 我不确定这是实现这一目标的最佳方法。 但是我想在第一个表中有一个触发器，这样每当有人编辑产品的值时，它会在第二个列的列中找到该产品名称表并更新该列的第二行。 我尝试过： 透过它的第一个表来使系统工作。 取消第二个表，让CodeIgniter在屏幕上正确显示。 到目前为止，我有：

DELIMITER $$ 创建TRIGGER after_update 更新桌面1后 FOR EACH ROW BEGIN INSERT INTO table2 SET action ='update'， WHERE`ProductName` = ProductName.Value //我希望它匹配productname值与列中的列一样，列名称。 //接下来，我希望它将Productvalue的值写入table2的第二行我可以将主键`id`添加到第二列，以简化 END $$ DELIMITER;

解决方案

创建触发器after_update 更新后的表1 FOR EACH ROW BEGIN INSERT INTO table2 SET action ='update'， WHERE`ProductName` = ProductName.Value //我希望它与productname值匹配表2中的列将是列名。 //接下来，我希望它将Productvalue的值写入table2的第二行我可以将主键`id`添加到第二列，以简化 END

DELIMITER;

Hi, The story is that I have two systems taking data from two tables. due to the lack of proper DB structure. Here's the first table for CodeIgniter Admin Page.

╔═════╦═════════════╦══════════════╗ ║ ID ║ ProductName ║ ProductValue ║ ╠═════╬═════════════╬══════════════╣ ║ 1 ║ A ║ 10 ║ ║ 2 ║ B ║ 20 ║ ║ 3 ║ C ║ 30 ║ ... ║ 26 ║ Z ║ 260 ║ ╚═════╩═════════════╩══════════════╝

and Here is the table for Another System. This is the structure the system is comfortable with:

╔════╦════╦════╦════╦════════╦═════╗ ║ A ║ B ║ C ║ D ║ ...... ║ Z ║ ╠════╬════╬════╬════╬════════╬═════╣ ║ 10 ║ 20 ║ 30 ║ 40 ║ ...... ║ 260 ║ ╚════╩════╩════╩════╩════════╩═════╝

I am not sure whats the best method to achieve this. But I am thinking of having a trigger in the first table so that whenever someone edits the value of the product it will find that product name in the column of the second table and update the second row of that column. What I have tried: Pivoting the first table for it to make work with the system. Unpivoting the second table for the CodeIgniter to show properly on the screen. So far I have:

DELIMITER $$ CREATE TRIGGER after_update AFTER UPDATE ON table1 FOR EACH ROW BEGIN INSERT INTO table2 SET action = 'update', WHERE `ProductName` = ProductName.Value //I want it to match the productname value with the column as in table2 that will the column name. // Next, I want it to write the value of Productvalue to the second row of table2 I can add a primary key `id` to the second column, to make things easier END$$ DELIMITER;

解决方案

CREATE TRIGGER after_update AFTER UPDATE ON table1 FOR EACH ROW BEGIN INSERT INTO table2 SET action = 'update', WHERE `ProductName` = ProductName.Value //I want it to match the productname value with the column as in table2 that will the column name. // Next, I want it to write the value of Productvalue to the second row of table2 I can add a primary key `id` to the second column, to make things easier END

DELIMITER;