error in your SQL syntax creating MySQL trigger

Question

I try create trigger

CREATE TRIGGER `aster_users2` after
update ON `aster_users` FOR EACH ROW
BEGIN  update event set flag=1 where
id=1; END;

but got next error

ERROR 1064 (42000): 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 'end' at line 6

have suggestion to solve this problem ?

Solution 1

Try removing the semi-colons from your statements.

If you'd like to keep your semi-colons,

DELIMITER $$
CREATE TRIGGER `aster_users2` after
update ON `aster_users` FOR EACH ROW
BEGIN  update event set flag=1 where
id=1;  
 END$$
DELIMITER ;

Solution 2

You can either:

• drop BEGIN and END (is only possible when there's a single statement in the body):

  CREATE TRIGGER `aster_users2` after
  update ON `aster_users` FOR EACH ROW
  update event set flag=1 where id=1;
  

  or

• add the DELIMITER specifier for the entire CREATE TRIGGER statement:

  DELIMITER |
  CREATE TRIGGER `aster_users2` after
  update ON `aster_users` FOR EACH ROW
  BEGIN
    update event set flag=1 where id=1;  
  END|
  DELIMITER ;
  

  Note the second DELIMITER, which restores the default statement delimiter.

EDIT – Explanation:

Generally you are using ; to delimit statements. But when it comes to compound statements like CREATE TRIGGER, which use BEGIN/END and allow you to include multiple statements in their bodies, the parser needs a way to distinguish the delimiters between the body's statements from the delimiter after the entire compound statement.

Thus you need to either refrain somehow from using ; inside the compound statement or tell the parser that the compound statement will use a different delimiter. The first option can also be achieved if you just drop ; before END, like @p.campbell has suggested.

Solution 3

Delimiters should be used.

DELIMITER $$

CREATE TRIGGER `aster_users2` AFTER
UPDATE ON `aster_users` FOR EACH ROW
BEGIN
  UPDATE event
  SET
    flag = 1
  WHERE
    id = 1;
END$$

DELIMITER ;

Comments

[Devart]

The first variant is appropriate here.

[codezoner]

I've wasted around hour. Thanks

[Henrik Erlandsson]

I like this answer more than the accepted answer, because it explains this illogical trap designed into MySQL.

[Arif I.]

@bynu022 You have not wasted your hour, you have actually invested your hour by learning from your mistakes.