xralf xralf - 12 days ago 6
MySQL Question

Syntax error when creating trigger from PHP code

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

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.

Comments