$wpdb and MySQL Create Trigger

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

Read More

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 );

Related posts

1 comment

  1. 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

    $sql_trigger = "....";
    mysqli_multi_query($wpdb->dbh,$sql_trigger);
    

Comments are closed.