Search code examples
phpmysqltriggersalter

Trigger Does not works - Alter table to add a new column before inserting in it

I am making a trigger to alter a table that adds a new column to it and inserts the value in it.

My code looks lyk dis:

 delimiter |

 CREATE TRIGGER addfield AFTER INSERT ON `entity_group_mapping`
 FOR EACH ROW BEGIN
     ALTER TABLE user_access ADD NEW.type_name INT(2) NOT NULL;
 END;

 |
 delimiter ;

It is giving me an error: #1103 - Incorrect table name 'NEW'

Solution

  • From the documentation:

    There is a hard limit of 4096 columns per table...Every table (regardless of storage engine) has a maximum row size of 65,535 bytes.

    Can you reach these limitations? Even if you cannot, I'd suggest you to think about design, and add records instead of new fields. Then you could try to PIVOT table - translate rows into fields, there are many pivot examples in the internet and of course in stackoverflow.