Bug #23727 Can't create compound statement trigger (BEGIN END)

• Submitted: 27 Oct 2006 16:14
• Modified: 27 Oct 2006 16:24
• Reporter: Gediminas Zukula
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: Ver 14.12 Distrib 5.0.21, for Win32
• OS: Windows (Windows XP Professional)
• CPU Architecture: Any
• Tags: TRIGGER BEGIN END

[27 Oct 2006 16:14] Gediminas Zukula

Description:
I was trying to create a trigger using compound statement, but QueryBrowser returned an error "You have an error in SQL syntax."

How to repeat:
CREATE TABLE `bug` (
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  `VALUE` INTEGER UNSIGNED,
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;

CREATE TRIGGER `bugTest` BEFORE UPDATE ON bug
FOR EACH ROW BEGIN
  SET NEW.VALUE = 3;
END;

Suggested fix:
don't use compound statements?

[27 Oct 2006 16:24] MySQL Verification Team

Thank you for the bug report.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `bug` (
    ->   `ID` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    ->   `VALUE` INTEGER UNSIGNED,
    ->   PRIMARY KEY(`ID`)
    -> )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> delimiter $$
mysql> CREATE TRIGGER `bugTest` BEFORE UPDATE ON bug
    -> FOR EACH ROW BEGIN
    ->   SET NEW.VALUE = 3;
    -> END$$
Query OK, 0 rows affected (0.09 sec)

mysql>