28

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?

Michael - sqlbot
  • 22,715
  • 2
  • 49
  • 76
Alex
  • 413
  • 2
  • 5
  • 7

3 Answers3

37

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.

Michael - sqlbot
  • 22,715
  • 2
  • 49
  • 76
1

As @Michael told you can't alter the trigger.

If you're using MySql Workbench it can create the drop and create statments for you. Right click on your table name and click Alter table option from there you can pick Trigger option and alter it. Although you cannot perform it from query. Table Name --> Right click --> Alter Table --> Out of 6 Tabs pick Triggers.

https://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

Hope this helps.

Spörri
  • 4,734
  • 15
  • 28
CoderGuy
  • 11
  • 2
0

Trying to build on @michael-sqlbot's answer.

Unfortunately, when working on a cluster the LOCK TABLES trick does not seems to be possible either.

For example with Percona

ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING

An alternative I've used is to create a new trigger named oldtrigger_v2, then remove the original one.

For example:

DELIMITER $$
CREATE TRIGGER on_xxx_delete_v2 BEFORE DELETE ON xxx
  FOR EACH ROW BEGIN
      DELETE FROM xxx_relationship WHERE xxx_id=old.id;
END $$
DELIMITER ;
DROP TRIGGER on_xxx_delete;

Obviously this only works if both triggers can work concurrently

(Yes I'm aware that my example makes no sense :p)

Romuald Brunet
  • 101
  • 5