0

I am creating a PHP script which imports a MySQL database into a newly created database. The script is exported from MySQLQ Workbench and contains the creation of the Triggers.

The code for importing the database works except the triggers.

Here is an example of how a trigger creation looks:


    DELIMITER $$

    USE `database_name`$$
    CREATE DEFINER = CURRENT_USER TRIGGER  `database_name`.`tablename_BEFORE_INSERT` BEFORE INSERT ON `tablename` FOR EACH ROW
    BEGIN
    IF(NEW.id IS NULL OR NEW.id = '') THEN
       set new.id = uuid();
    END IF;
    END$$
    DELIMITER ;

This works if i copy and paste this into PHPMyAdmin but i cannot get this executed via php ($mysqli->query(..), $mysqli->multi_query) etc...

The problem is because of the DELIMITER, i have changed the $$ to other characters but also that doesn't work. I saw other posts where people say DELIMITER isn't working via PHP but i think there has to be a way to create those triggers via PHP?

BLB
  • 23
  • 1
  • 6
  • 1
    DELIMITER is a [mysql client command](https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html). It's only recognized in the mysql client. DELIMITER and other client commands aren't recognized when you execute SQL statements via API. But it's also true that you don't need it when you create a trigger or routine via API. – Bill Karwin Mar 26 '20 at 17:35
  • get rid if the use databasename, this should be established by the connection string and the DELIMITES – nbk Mar 26 '20 at 17:35
  • i also tried without DELIMITER $$ and USE `database_name`$$ and END$$ and DELIMITER ;, but that is also not working (i think because of set new.id == uuid(); – BLB Mar 26 '20 at 17:43
  • Why not just populate the UUID in PHP during insert? – tadman Mar 26 '20 at 17:52
  • I am not sure if i don't understand you or you don't understand the trigger? I create a database trigger once so on every insert MySQL creates a new uuid, how can i solve that in PHP while importing a blank database? – BLB Mar 26 '20 at 18:00

0 Answers0