CODEWITHSUNDEEP
mysqltriggerstransactions
Technetium
Technetium

Reputation: 6158

Alter MySQL Trigger within Transaction

After an ALTER TABLE command, it is common to need a update a trigger that is firing based upon data being inserted, updated, or deleted on the altered table. Unfortunately, DROP TRIGGER and CREATE TRIGGER cause implicit commits, so one cannot simply place these two commands in a single transaction followed by a commit.

Is there a way to update a MySQL Trigger so that either the old or new variant of the trigger is active whenever data within the table with the trigger is manipulated?

Upvotes: 1

Views: 831

Answers (1)

Vojtech Kurka
Vojtech Kurka

Reputation: 905

You can modify trigger atomically using table locking:

LOCK TABLES `mytable` WRITE;  

 DELIMITER $$    
   DROP TRIGGER `mytable_tri` $$  
   CREATE TRIGGER `mytable_tri` AFTER INSERT ON `mytable`  
      FOR EACH ROW BEGIN  
      /* ...... trigger code ......... */  
   END;  
   $$  
 DELIMITER ;  

 UNLOCK TABLES;

It works in MySQL 5.6 and newer.

I have written a blogpost about that some time ago: http://vkwww.blogspot.com/2014/07/alter-trigger-in-mysql.html

Upvotes: 3

Related Questions