current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

5 Answers 5

up vote 2 down vote accepted

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.

share|improve this answer
up vote 0 down vote

Since you are creating the trigger using code, you might not need to set/reset the DELIMITER. Changing the DELIMITER is important for CLIs.

Just ignore those lines in your php code that changes them.

Your code becomes only:

$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);
share|improve this answer
    
SQLSTATE[HY000]: General error: 1193 Unknown system variable 'name' –  xralf Sep 13 '14 at 18:12
up vote 0 down vote

try that :

  $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);
share|improve this answer
    
SQLSTATE[HY000]: General error: 1193 Unknown system variable 'name' –  xralf Sep 13 '14 at 18:04
    
have you name in your employee table ? –  Karim Daraf Sep 13 '14 at 18:10
    
yes "name varchar(255) not null" ... btw. the code works in MySQL. –  xralf Sep 13 '14 at 18:14
    
yes , you tagged mysql , have you other then mysql ? –  Karim Daraf Sep 13 '14 at 18:17
    
What do you mean by "have you other than mysql?" –  xralf Sep 13 '14 at 18:19
up vote 0 down vote

Since you are creating the trigger using code, you might not need to set/reset the DELIMITER.

Just ignore those lines in your php code that changes them.

Your code becomes only:

$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);
share|improve this answer
    
SQLSTATE[HY000]: General error: 1193 Unknown system variable 'name' –  xralf Sep 13 '14 at 18:10
    
See the edit please. –  Peter Sep 13 '14 at 18:11
    
What have you changed? –  xralf Sep 13 '14 at 18:12
    
The name is escaped. –  Peter Sep 13 '14 at 18:15
    
error 1193 is still present –  xralf Sep 13 '14 at 18:17
up vote 0 down vote

Mayby this helps:

$sql = "delimiter //\n";
$pdo->exec($sql);
$sql = "create trigger avoid_empty_employee_insert before insert on employee\n
      for each row\n 
        begin\n
        if new.name = '' then\n 
        set new.name = null;\n
        end if;\n
      end//\n";
$pdo->exec($sql);
$sql = 'delimiter ;';
$pdo->exec($sql);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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