CODEWITHSUNDEEP
mysqlvariablestriggersadditiondynamic-columns
bakz
bakz

Reputation: 89

adding MySQL column to a table using trigger

I have a table that looks like this:

Counters:

| client_id | provider1 | provider2 | provider3 |

_ - _ - _- _ - _ - _- _ - _ - _

the column names [provider1,provider2,...] is the id of provider which is stored in another table called "providers".

what i need is, when a new provider is inserted into the "proviers" database a trigger would automatically add another column in the "Counters" table, but I can't seem to be able to get it.

this is what i have so far:

CREATE TRIGGER `providers_AFTER_INSERT` AFTER INSERT ON `providers` FOR EACH ROW
    ALTER TABLE `counters` ADD NEW.prov_id int;

the error is at the '.' between 'NEW; and 'prov_id' with the error:

Syntax error: unexpected '.' (dot)

I am guessing it needs a string value as the name of the column so i searched around and found a way to pass it as a string:

CREATE TRIGGER `providers_AFTER_INSERT` AFTER INSERT ON `providers` FOR EACH ROW
    ALTER TABLE `counters` ADD CAST(NEW.prov_id AS CHAR CHARACTER SET utf8) int;

but this shows me an error at the 'ADD' saying it is unexpected.

also tried surrounding the variable with brackets {NEW.prov_id} but to no avail, it gives the same error at the 'ADD' clause.

also, while searching I also in some posts that says it does not work while some say it does work, and those who say it works don't use a variable for the column name.

how can i fix this error? and is it even doable?

my MySQL is v5.0 I can upgrade it, if necessary.

Thank you in advnace

Upvotes: 0

Views: 2187

Answers (1)

Psi
Psi

Reputation: 6783

Short answer: Impossible.

ALTER TABLE is a so-called "DDL-statement" (data definition language), which differs from "DML-statements" (data manipulation language).

DDL-Statements are not allowed in triggers, afaik.

You would need dynamic SQL to do this, because table names are not variable in DDL.

Unfortunately, dynamic SQL is not allowed in triggers. When trying to execute a prepare statement in a trigger, mysql throws an error:

Dynamic SQL is not allowed in stored function or trigger

More in the mysql documentation: https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html

Upvotes: 2

Related Questions