I have multiple tables with similar structure
+-------+--------+-------------+--------+-------------+
| Col1 | Col2 | Col3 | Col4 | Col5 |
+-------+--------+-------------+--------+-------------+
| Item1 | value2 | Valu3 | Value4 | Value5 |
+-------+--------+-------------+--------+-------------+
Col2 through to Col5 are disabled on the frontend. I want them to get filled via trigger as soon as Col1 is selected.
I want to use this table to get the values of Col2 through Col5 by matching the value of Col1
+-------+--------+-------------+--------+-------------+
| Col1 | Col2 | Col3 | Col4 | Col5 |
+-------+--------+-------------+--------+-------------+
| Item1 | value2 | Valu3 | Value4 | Value5 |
+-------+--------+-------------+--------+-------------+
| Item2 | | Item2Value3 | | |
+-------+--------+-------------+--------+-------------+
| Item3 | | | | Item3Value5 |
+-------+--------+-------------+--------+-------------+
I am thinking of doing triggers on the tables but I'd have to create a trigger for all the tables in the database. is there an easy way to do a bulk update?
This is the trigger Code I am planning on using. (which isn't working at the moment)
I need help fixing it.
Thanks in advance.
What I have tried:
DELIMITER $$
CREATE
TRIGGER `onUpdate` AFTER INSERT ON `table1`
FOR EACH ROW BEGIN
UPDATE
SET
`Col2` = `table2`.`Col2`,
`Col3` = `table2`.`Col3`,
`Col4` = `table2`.`Col4`,
`Col5` = `table2`.`Col5`
WHERE
table2.`Col1` = Col1;
UPDATE
SET
`Col2` = `table3`.`Col2`,
`Col3` = `table3`.`Col3`,
`Col4` = `table3`.`Col4`,
`Col5` = `table3`.`Col5`
WHERE
table3.`Col1` = Col1;
UPDATE
SET
`Col2` = `table4`.`Col2`,
`Col3` = `table4`.`Col3`,
`Col4` = `table4`.`Col4`,
`Col5` = `table4`.`Col5`
WHERE
table4.`Col1` = Col1;
......
END;
$$