mysql触发器实例

有一个学生管理程序，涉及到学生的证书，在这个程序里，学生可以自己添加一个或多个证书，把自己的证书信息添加到系统里，之后管理员对这些信息进行审核，确定证书的真实性。当学员的所有证书都审核通过后，学员的状态就会变为诚信，反之，只要有一个证书没有审核通过，学员状态就变为非诚信。或者是学员又重新上传了一个证书后，学员状态又变为非诚信，当管理员审核通过后，学员状态即改为程序。

学员表结构：（这里为了简单，省去或修改了部分信息）

学员ID，名称，性别，状态

证书表结构：

证书ID，名称，状态，学员ID

首先来创建这两个表，如下是建表语句：

view plaincopy to clipboardprint?
/**
* 创建一个学生表
*  
*/
CREATE TABLE `students` (  
`id` INTEGER NOT NULL AUTO_INCREMENT,  
`name` VARCHAR(45) NOT NULL,  
`sex` VARCHAR(1) NOT NULL,  
`status` INTEGER NOT NULL,  
PRIMARY KEY (`id`)  
)  
ENGINE = InnoDB;  

/**
* 创建一个证书表
*  
*/
CREATE TABLE `certs` (  
`id` INTEGER NOT NULL AUTO_INCREMENT,  
`name` VARCHAR(45) NOT NULL,  
`status` INTEGER NOT NULL,  
`student_id` INTEGER NOT NULL,  
PRIMARY KEY (`id`)  
)  
ENGINE = InnoDB;

 /**

插入学生数据
*
*/
INSERT INTO students (id, name, sex, status) values(1, 'liuhongjun', 'M', 1);
INSERT INTO students (id, name, sex, status) values(2, 'pandeyou', 'M', 1);
/**
* 插入证书数据
*
*/
INSERT INTO certs (id, name, status, students_id) values (1, 'ACCP', 1, 1);
INSERT INTO certs (id, name, status, students_id) values (2, 'OSTA', 1, 1);

数据说明：

建好的的数据都是完整的，学员liuhongjun，对应了有两个证书，分别是ACCP和OSTA证书，证书的状态都为1，也就是审核通过的。

所以学员liuhongjun的状态是1，如下：

view plaincopy to clipboardprint?
+----+------------+-----+--------+  
| id | name       | sex | status |  
+----+------------+-----+--------+  
| 1 | liuhongjun | F   |      1 |  
| 2 | pandeyou   | M   |      1 |  
+----+------------+-----+--------+  

+----+------+--------+-------------+  
| id | name | status | students_id |  
+----+------+--------+-------------+  
| 1 | ACCP |      1 |           1 |  
| 2 | OSTA |      1 |           1 |  
+----+------+--------+-------------+

接下来，开始建立触发器，建立触发器代码如下：（触发器语法请参见：http://dev.mysql.com/doc/refman/5.1/zh/triggers.html#create-trigger）

view plaincopy to clipboardprint?
/**
* 创建证书触发器
*  
* 名称：trigger_certs
* 触发时间：在certs表更新以后（AFTER）
* 关联表：students
* 执行的事务：如果该学员的证书全部状态都为1时，更新学员的状态为1，否则更新学员的状态为0。
*/
delimiter //  
CREATE TRIGGER trigger_certs AFTER UPDATE ON certs  

FOR EACH ROW BEGIN  
SET @studentsId = OLD.students_id;  
IF OLD.status != NEW.status THEN  
    SELECT COUNT(id) INTO @total FROM certs c WHERE c.students_id = NEW.students_id AND c.status = 0 LIMIT 1;  
    IF @total = 0 THEN  
      UPDATE students s SET s.status = 1 WHERE s.id = @studentsId;  
    ELSE  
      UPDATE students s SET s.status = 0 WHERE s.id = @studentsId;  
    END IF;  
END IF;  
END;
结果：
+----+------------+-----+--------+
| id | name       | sex | status |
+----+------------+-----+--------+
| 1 | liuhongjun | F   |      1 |
| 2 | pandeyou   | M   |      1 |
+----+------------+-----+--------+

学员liuhongjun的状态又改为了1。