CODEWITHSUNDEEP
mysqlalter-tabledatabase-trigger
HartleySan
HartleySan

Reputation: 7830

Is there any way to detect when an ALTER TABLE statement is executed in MySQL?

Is there any way to detect when an ALTER TABLE statement is executed in MySQL? For example, if the following statement were executed on some_table, is there any way to detect that the column name changed from column_name_a to column_name_b and log it in another table in the DB?

ALTER TABLE `some_table` 
CHANGE COLUMN `column_name_a` `column_name_b` VARCHAR(255) NULL DEFAULT NULL;

Thanks.

Upvotes: 1

Views: 492

Answers (1)

luksch
luksch

Reputation: 11712

To my knowledge it is unfortunately not possible to put triggers on the INFORMATION_SCHEMA tables, since they are strictly spoken views and triggers can't be made to work on views. If triggers would be possible on the INFORMATION SCHEMA, then you could have a trigger on updates of the INFORMATION_SCHEMA.COLUMNS table to identify name changes.

However, what you can do is one of the following things:

option 1) Maintain a real table with all column names. Then create a function that checks for a discrepancy between the INFORMATION_SCHEMA.COLUMNS table abd your table. If there is one, you know the name has changed. You need to copy over the new name to your column name table and do whatever else you wanted to do upon name change.

The function to check for discrepancies then must be run periodically via the mysql scheduler in order to detect name changes as quickly as possible. Note that this is not a real time solution. There will be a lag between the ÀLTER TABLE command and its detection. If this is unacceptable in your scenario you need to go with

option 2) Do not call ÀLTER TABLE directly, but wrap it in a function. Within this function you can also call other functions to achieve what you need to achieve. If may be worth while to formulate the needed steps in a higher programming language that you use to drive your application. If this is not possible, you will be limited to the possibilities that are offered in functions/procedures in the mysql environment.

Sorry to not have a simpler way of doing this for you.

Upvotes: 1

Related Questions