问个触发器的问题

[wminjay]

-- 数据库: '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;

请问这个触发器如何实现？

[wminjay]

补充说明下 如果 student 表插入

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)

[zuoxingyu]

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;

[曾经的阿飞]

[Quote=引用 2 楼 zuoxingyu 的回复:]

SQL code

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
……
[/Quote]
强

[ACMAIN_CHM]

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

[wwwwb]

直接用MYSQL的N DUPLICATE KEY 就可以了
INSERT INTO class (`class` ,`count`) values (new.class,1) ON DUPLICAT
E KEY UPDATE `count`=`count`+1 ;