用于透视表的Mysql触发器 [英] Mysql trigger for pivoted table

问题描述

故事是我有两个系统从两个表中获取数据。由于缺乏适当的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;

这篇关于用于透视表的Mysql触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！