Use Create Trigger to create New table using calculated values from common groupid column values

Question

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:

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

Any help would be greatly appreciated!!!

Answer 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 ;