5

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 );
  • what result do you get? – Mark Kaplun Dec 22 '13 at 15:26
  • Using xampp - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER // .... – chabi Dec 22 '13 at 15:35
  • this sounds like an error from the mysql engine. your syntax might actually be wrong, at least when it is part of php code (// is comment....) – Mark Kaplun Dec 22 '13 at 15:44
  • I already tried different delimiters such as DELIMITER $$ before the create-trigger-query and ended it with this one - still the same issue. Please see the above edit. – chabi Dec 22 '13 at 16:00
5

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);
  • So in conclusion I was right that mysqli is the only working solution. – chabi Dec 22 '13 at 16:22
  • I am sure it doesn't matter much but technically you were using the wrong API, or the API you needed don't exist, which is different then "wordpress do not accept my query", as it was mysql that didn't accept them, and might be an important distinction for other people. – Mark Kaplun Dec 22 '13 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.