Syntax error when creating trigger from PHP code

Question

I'd like to create a trigger from the following PHP code.

  $sql = 'delimiter $$';
  $pdo->exec($sql);
  $sql = 'create trigger avoid_empty_employee_insert before insert on `employee`
          for each row begin
            if name = "" then set name = null; end if;
          end$$';
  $pdo->exec($sql);
  $sql = 'delimiter ;';
  $pdo->exec($sql);

When I run the the code in MySQL it works and the trigger is created.

PHP shows the following error.

SQLSTATE[42000]: Syntax error or access violation: 1064 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 $$' at line 1

How can I fix it?

Answer 1

Definitely do not try to change the delimiter when you're executing the statement via the API. DELIMITER is a mysql client built-in command, it is not recognized by the MySQL server-side parser.

You don't need it anyway. The purpose of DELIMITER is to remove the ambiguity of semicolons that may appear within the body of a trigger or stored routine. Since the API is for executing one statement at a time, there's no ambiguity. The SQL parser just treats the whole string as one statement anyway.

Likewise, do not end the create trigger statement with $$. You don't need any statement terminator, but the SQL parser accepts ; as an optional statement terminator because so many people put it there even though they don't have to.

The next problem is that you when you use column names in a trigger, you have to prefix them with either NEW. or OLD. -- in an insert trigger, you can only use NEW. If you don't prefix the column, MySQL assumes you meant to set a system variable like tmpdir or slow_query_log.

If you are still getting the 1193 error, I suggest that you didn't change both references to the name column to NEW.name.

I tested the following using PHP 5.4.24 and MySQL 5.6.20, and it worked:

$sql = "create trigger avoid_empty_employee_insert before insert on `employee`
      for each row begin
          if NEW.name = '' then set NEW.name = null; end if;
      end";
$pdo->exec($sql);

You don't need to delimit the column name of name, because it is not a MySQL reserved word. The set of reserved words is documented.