1 /**
2 **创建表
3 */
4 CREATE TABLE test1(a1 INT);
5 CREATE TABLE test2(a2 INT);
6 CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
7 CREATE TABLE test4(
8 a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
9 b4 INT DEFAULT 0
10 );
11 /**
12 **创建触发程序
13 */
14 DROP TRIGGER IF EXISTS testref;
15 DELIMITER //
16 CREATE TRIGGER testref BEFORE INSERT ON test1
17 FOR EACH ROW BEGIN
18 INSERT INTO test2 SET a2 = new.a1;
19 DELETE FROM test3 WHERE a3 = new.a1;
20 UPDATE test4 SET b4 = b4 + 1 WHERE a4 = new.a1;
21 END //
22 DELIMITER ;
23
24 /**
25 ** 插入初始数据
26 */
27 INSERT INTO test3(a3) VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
28
29 INSERT INTO test4(a4) VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
30
31 /**
32 ** 测试触发过程
33 */
34 INSERT INTO test1 VALUES (1),(3),(1),(7),(1),(8),(4),(4);
