Do $wpdb
‘s methods not accept a MySQL CREATE TRIGGER
query? I tried it with $wpdb->query( $sql_trigger )
over and over again without success. Throwing the create-trigger-query on phpMyAdmin the trigger is created, so the query seems to be fine.
$sql_trigger = "DELIMITER //
CREATE TRIGGER triggerName
BEFORE UPDATE
ON `table_name`
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END; //
DELIMITER ;
";
Just read EddyR used mysqli to create his trigger. https://wordpress.stackexchange.com/a/34653
Is there no way to use WP’s ‘native functions’ to create a mysql trigger or am I doing something wrong?
edit:
Separating the DELIMITER settings and the query like this (or even changing the DELIMITER from //
to $$
)did not change anything. still the same issue.
$sql_before_trigger = "DELIMITER $$";
$sql_after_trigger = "DELIMITER ;";
$wpdb->query( $sql_before_trigger );
$wpdb->query( $sql_trigger );
$wpdb->query( $sql_after_trigger );
In the question you linked to, the solution was to use
mysqli_multi_query
as the API for executing the SQL. $wpdb desen’t have an API to do a multi query therefor you can’t use it directly, but you can get the handle to the mysql interface from$wpdb->dbh
and do something like