Can you modify an existing mysql trigger after it has been created?

Question

In mysql I can create a trigger, and then show information about it like this:

mysql> show triggers like 'fooTrigger';

This command gives output that looks an awful lot like a select statement, with a row showing the matching trigger. Is it possible to update a column on the row it shows me?

For example, one column is named Statement, and it defines what happens when the trigger is activated. Is it possible to change the Statement field for fooTrigger so the trigger does something different? Or do I need to drop and re-create the trigger?

Answer 1

As of MySQL 5.5, you must drop and re-create the trigger.
It is not possible to update the Statement column without dropping the trigger.

Documentation: CREATE TRIGGER DROP TRIGGER

You may also access information about triggers using the INFORMATION_SCHEMA tables:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS

But, as these tables are actually views, you can't use UPDATE on them.
It's just a more convenient way to access and manipulate the information than using SHOW TRIGGERS.

Documentation: INFORMATION_SCHEMA.TRIGGERS