1

I have a TABLE_A in Database1 the contains rows with a common group id. I am wanting to use the CREATE TRIGGER function to generate a new TABLE_B in the same Database1 that contains calculated values (summed/multipled ect.) of certain columns with the same group id. and to group these all together. Using Mysql 5.5

ie:

enter image description here

TALBE_B (New Table Updated as TABLE_A data changes) (using CREATE TRIGGER..? )

enter image description here

Any help would be greatly appreciated!!!

  • Let's see the UPDATE statement you would use if you had to do it manually. Then modify that into a INSERT ... ON DUPLICATE KEY UPDATE that will take care of either inserting or updating, and let's see that. – Rick James Apr 25 '15 at 2:44
  • Why in the result 'id' for Mike is 2 ? Also is the TABLE_B structure (DDL) already created and we are just supposed to INSERT values in TABLE_B. OR you want to SELECT, CREATE and INSERT all together ? – Koustuv Chatterjee Apr 25 '15 at 13:39
1

We can use MySQL CREATE TABLE AS statement to create a TABLE_B from existing TABLE_A by copying the existing table's columns

CREATE TABLE TABLE_B AS   
SELECT  id, name, SUM(cost) AS 'cost', organization
     FROM TABLE_A            
     GROUP BY name, organization; 

In MySQL, we cannot have after INSERT or UPDATE description together, like in Oracle unfortunately. So you have to create two TRIGGERs

delimiter //
CREATE TRIGGER update_tableb
 AFTER INSERT 
  ON TABLE_A FOR EACH ROW
     BEGIN

   -- variable declarations

   -- trigger code

    END;//
delimiter ;
  • Thank you MySQLRockstar. Will this not simply create a table with current data from TABLE_A? I don't believe data would be refreshed to TABLE_B as it changes in TABLE_A – yip Apr 25 '15 at 23:03
  • You are right, in that case we need to create TRIGGER with AFTER UPDATE/INSERT – Koustuv Chatterjee Apr 26 '15 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.