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