I'd like to create a trigger from the following
$sql = 'delimiter $$';
$sql = 'create trigger avoid_empty_employee_insert before insert on `employee`
for each row begin
if name = "" then set name = null; end if;
$sql = 'delimiter ;';
SQLSTATE: 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
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
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
If you are still getting the 1193 error, I suggest that you didn't change both references to the
name column to
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.