Click here to Skip to main content
14,921,505 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
    /*!50017 DEFINER = 'root'@'%' */
    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;
$$
Posted
Updated 10-Apr-18 17:48pm

1 solution

I'm not entirely sure what you're after but if the separate triggers have common logic, why not create a procedure and call that procedure in each trigger. This wuld prevent repeating the same logic across all the triggers.

Have a look at MySQL :: MySQL 5.7 Reference Manual :: 13.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax[^]
   
Comments
Member 13773036 11-Apr-18 23:00pm
   
Yes. I will be doing that next. But I am stuck at this stage. MySQL is throwing me an error whenever I am adding more than one table update script.
Wendelius 11-Apr-18 23:27pm
   
What is the error you get?
Member 13773036 12-Apr-18 1:44am
   
Mysql error
Wendelius 12-Apr-18 23:04pm
   
Please post the full error text you get.

Without seeing the error, it's quite impossible to say what is the reason for it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900