9

I want to create a trigger in MySQL. I run following commands:

mysql> delimiter //
mysql> CREATE TRIGGER before_insert_money BEFORE INSERT ON money
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE accounts SET balance=10.0;
    -> END;
    -> //
Query OK, 0 rows affected (0.19 sec)

But when I run above SQL in phpmyadmin I get this error:

#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 '' at line 4

what's wrong here? How do I create a trigger?

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Moein Hosseini
  • 4,309
  • 15
  • 68
  • 106

1 Answers1

13

This is caused by not changing the delimiters temporarily as you did via CLI. Try either:

CREATE TRIGGER before_insert_money BEFORE INSERT ON money
FOR EACH ROW UPDATE accounts SET balance=10.0;

or

delimiter //
CREATE TRIGGER before_insert_money BEFORE INSERT ON money
FOR EACH
ROW
BEGIN
    UPDATE accounts SET balance=10.0;
END;
//
delimiter ;

See: This question and this question.

Community
  • 1
  • 1
Brainless Box
  • 587
  • 9
  • 16