I’m creating a plugin and I want to automate the creation of a MySQL Trigger on a table. The problem seems to be that $wpdb->query doesn’t appear to accept either multipart statements and/or formatted queries.
If I print and run the following query in phpMyAdmin it returns success. I also noticed if I formatted the query all on 1 line it would still report success but in this case the trigger is NOT created.
$wpdb->query(
"DELIMITER //
DROP TRIGGER IF EXISTS {$wpdb->get_blog_prefix($site->id)}post_queue_insert//
CREATE TRIGGER {$wpdb->get_blog_prefix($site->id)}queue_insert BEFORE INSERT ON {$wpdb->get_blog_prefix($site->id)}posts
FOR EACH ROW BEGIN
INSERT INTO {$wpdb->base_prefix}post_queue (action_id, action_type, action_status, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, NEW.post_type, NEW.post_status, UNIX_TIMESTAMP(now()), 0, {$site->id});
END//
DELIMITER ;"
);
Is there another function I can use or another way to format the query to run successfully?
After much trial and error I finally figured out a solution using mysqli. I consider it a acceptable solution since the query will only be run on plugin updates.