Mysql trigger for pivoted table

See more:

MySQL

BI

CodeIgniter

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;