-- 数据库: 'jay'
---- ----------------------------------------------------------
-- 表的结构 'class'
--
CREATE TABLE IF NOT EXISTS class (
id int(11) NOT NULL AUTO_INCREMENT,
class int(11) NOT NULL,
count int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 'student'
--CREATE TABLE IF NOT EXISTS student (
id int(11) NOT NULL AUTO_INCREMENT,
stuname varchar(10) NOT NULL,
class int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;要实现
当student 表 插入一条数据时 如
INSERT INTO `jay`.`student` (`id`, `stuname`, `class`) VALUES (NULL, 'wminjay', '1'); class表也执行相应操作
INSERT INTO `jay`.`class` (
`id` ,
`class` ,
`count`
)
VALUES (
NULL , '1', '1'
);当插入另一条数据时 如
INSERT INTO `jay`.`student` (
`id` ,
`stuname` ,
`class`
)
VALUES (
NULL , 'jay', '1'
);
class 表进行
UPDATE `jay`.`class` SET `count` = count+1 WHERE `class`.`id` =1;
请问这个触发器如何实现?
INSERT INTO `jay`.`student` (
`id` ,
`stuname` ,
`class`
)
VALUES (
NULL , 'wminjay', '1'
), (
NULL , 'jay', '1'
), (
NULL , 'wow', '2'
), (
NULL , 'cwow', '2'
), (
NULL , 'twow', '2'
), (
NULL , 'firstblood', '3'
), (
NULL , 'doublekilled', '3'
), (
NULL , 'cwow', '2'
), (
NULL , 'perl', '4'
), (
NULL , 'jay', '1'
);
则student和class表内数据应该如下
mysql> select * from student;
+----+------------+-------+
| id | stuname | class |
+----+------------+-------+
| 1 | wminjay | 1 |
| 2 | jay | 1 |
| 3 | wow | 2 |
| 4 | cwow | 2 |
| 5 | twow | 2 |
| 6 | firstblood | 3 |
| 7 | doublekill | 3 |
| 8 | cwow | 2 |
| 9 | perl | 4 |
| 10 | jay | 1 |
+----+------------+-------+
10 rows in set (0.00 sec)mysql> select * from class;
+----+-------+-------+
| id | class | count |
+----+-------+-------+
| 1 | 1 | 3 |
| 2 | 2 | 4 |
| 3 | 3 | 2 |
| 4 | 4 | 1 |
+----+-------+-------+
4 rows in set (0.00 sec)
drop trigger if exists trg_1;
CREATE TRIGGER TRG_1 BEFORE INSERT ON student
FOR EACH ROW
BEGIN
DECLARE ID1 int;
if(exists(select class from class where class=new.class)) then
select id into ID1 from class where class=new.class;
UPDATE class SET `count` = `count`+1 WHERE `class`.`id` =ID1;
else
INSERT INTO `class` (`id` ,`class` ,`count`)VALUES (NULL , new.class, '1');
end if ;
END;
-> id int(11) NOT NULL AUTO_INCREMENT,
-> class int(11) NOT NULL,
-> count int(11) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE IF NOT EXISTS student (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> stuname varchar(10) NOT NULL,
-> class int(11) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)-- Class 表中根本不需要这个id int(11) NOT NULL AUTO_INCREMENT,因为你的 class int(11) NOT NULL,本身就是事实上的主键!
mysql> drop table class;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE TABLE IF NOT EXISTS class (
-> class int(11) PRIMARY KEY,
-> count int(11) NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.29 sec)-- 创建触发器
mysql> CREATE TRIGGER tr_ai_student AFTER INSERT ON student
-> FOR EACH ROW
-> INSERT INTO class (`class` ,`count`) values (new.class,1) ON DUPLICAT
E KEY UPDATE `count`=`count`+1 ;
Query OK, 0 rows affected (0.06 sec)-- 测试数据
mysql> INSERT INTO `student` (`id`, `stuname`, `class`) VALUES (NULL, 'wminjay', '1');
Query OK, 1 row affected (0.16 sec)mysql> select * from student;
+----+---------+-------+
| id | stuname | class |
+----+---------+-------+
| 1 | wminjay | 1 |
+----+---------+-------+
1 row in set (0.02 sec)mysql> select * from class;
+-------+-------+
| class | count |
+-------+-------+
| 1 | 1 |
+-------+-------+
1 row in set (0.00 sec)mysql> INSERT INTO `student` (`id` ,`stuname` ,`class`)VALUES (NULL , 'jay', '1');
Query OK, 1 row affected (0.03 sec)mysql> select * from student;
+----+---------+-------+
| id | stuname | class |
+----+---------+-------+
| 1 | wminjay | 1 |
| 2 | jay | 1 |
+----+---------+-------+
2 rows in set (0.00 sec)mysql> select * from class;
+-------+-------+
| class | count |
+-------+-------+
| 1 | 2 |
+-------+-------+
1 row in set (0.00 sec)mysql>
INSERT INTO class (`class` ,`count`) values (new.class,1) ON DUPLICAT
E KEY UPDATE `count`=`count`+1 ;