Mysql如何在触发器内执行多条sql

[涵宇菲子]

hi 各位大神：我想在一个触发器中执行多条语句，但就是不成功。官方的例子都执行不了。
官方例子：
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);


CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;



//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 3

mysql版本为:5.5.32-0ubuntu0.12.10.1 - (Ubuntu)

但我用以前的方式是可以运行成功的：

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
INSERT INTO test2 SET a2 = NEW.a1;

多谢各位了。

[ACMAIN_CHM]

delimiter // CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; // delimiter ;

[九月茅]

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

DELIMITER $$

USE `test`$$

DROP TRIGGER /*!50032 IF EXISTS */ `testref`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `testref` BEFORE INSERT ON `test1` 
    FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
 END;
$$

DELIMITER ;


-- 执行过程如下：

mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test4(
    ->   a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   b4 INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> USE `test`$$
Database changed
mysql> 
mysql> DROP TRIGGER /*!50032 IF EXISTS */ `testref`$$
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE
    ->     /*!50017 DEFINER = 'root'@'localhost' */
    ->     TRIGGER `testref` BEFORE INSERT ON `test1` 
    ->     FOR EACH ROW BEGIN
    ->     INSERT INTO test2 SET a2 = NEW.a1;
    ->     DELETE FROM test3 WHERE a3 = NEW.a1;
    ->     UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
    ->  END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DELIMITER ;
mysql> 

[九月茅]

在命令行里面执行存储过程，需要写好间隔符。

[涵宇菲子]

多谢各位，因为以前没弄过，所以一直报错，多谢指点。