What is the right way to modify a MySQL trigger?

Question

As you probably know, there's no syntax that modifies a MySQL trigger.

To do that, you need to execute DROP TRIGGER and then re-create it again with the new definition.

What is the right/best way of doing this, considering the following:

• You cannot encapsulate these two statements in a transaction as that will be pointless (both DROP TRIGGER and CREATE TRIGGER invoke implicit transactions)
• You cannot use LOCK TABLES READ as an error is triggered
• Just between your DROP and CREATE TRIGGER, some other session might insert/update/delete row(s) which won't be handled by neither of your new nor old triggers.

Answer 1

When testing before I posted, I overlooked what type of LOCK I'm acquiring, it was READ.

So it seems using WRITE lock does the job:

delimiter $$

LOCK TABLES table1 WRITE $$

DROP TRIGGER IF EXISTS after_insert_on_table1 $$

CREATE TRIGGER after_insert_on_table1 AFTER INSERT ON table1
FOR EACH ROW
    BEGIN
        ...
    END
$$

UNLOCK TABLES $$

delimiter ;

So my recommendation is to always use this sequence when updating/modifying triggers.