How to create MySQL trigger in custom WP plugin installation

Question

I am writing a custom WP plugin. While installing the plugin, custom tables are created in WP database. What I want is some WP method to create a trigger for my table. It seems that wpdb->query() is not working for creating triggers. My code is:

global $wpdb;
$sql = "
        DROP TRIGGER IF EXISTS `delete_distributer_related_data`;
                DElIMITER //
        CREATE TRIGGER `delete_distributer_related_data` AFTER DELETE ON   `naqdina_distributers`
        FOR EACH ROW delete from naqdina_addresses where address_reference_type ='distributer' and
        id = OLD.id //
                DElIMITER";

$wpdb->query($sql);

Any help is much appreciated.

Answer 1

Your trigger syntax is wrong. The best approach is to write the trigger directly on mysql however if you need to execute it via the PHP end then you may check this thread Create mysql trigger via PHP?

Now coming back to the trigger part from the mysql terminal or PHPMyadmin the trigger should look like

DROP TRIGGER IF EXISTS delete_distributer_related_data;
delimiter //
create trigger delete_distributer_related_data
after insert on naqdina_distributers
for each row
begin
   delete from naqdina_addresses 
   where address_reference_type ='distributer' and id = OLD.id ;
END; // 
delimiter ;

Note that while you execute the trigger from PHP end using mysql_ or mysqli_ function you do not need the delimiter section.