Is it possible to modify an existing trigger definition in MySQL?

I am wondering if it is possible to update a trigger definition in MySQL. For example, I have a trigger T and I want to add some new functionality to it.

My assumption is that I need to drop and recreate it.

What are the best practices in the database administration for such scenarios?

It sounds like you're asking whether it's possible to alter a trigger in an atomic operation, where, if the new definition fails, you don't lose the old one... similar to CREATE OR REPLACE VIEW, which replaces the view definition if the new definition is valid, but leaves the old one in place, if you can't replace it.

Unfortunately, there's no ALTER TRIGGER or CREATE OR REPLACE TRIGGER in MySQL.

I would suggest that the best practice is to lock the table where the trigger lives, so no rows are impacted with the trigger absent. Dropping and adding triggers while a table is locked is allowed.

mysql> LOCK TABLES t1 WRITE; -- the next prompt appears once you've obtained the lock
mysql> DROP TRIGGER t1_bi; 
mysql> DELIMITER $
mysql> CREATE TRIGGER ti_bi BEFORE INSERT ON t1 FOR EACH ROW 
       BEGIN
       ...
       END $
mysql> DELIMITER ;
mysql> UNLOCK TABLES;

Update: MariaDB, in version 10.1.4, added support CREATE OR REPLACE TRIGGER to their drop-in replacement for MySQL.

https://mariadb.com/kb/en/mariadb/create-trigger/

Oracle's MySQL as of 5.7 still relies on the solution above.

StackExchange Database Administrators Q#78438, answer score: 37