MYSQL——触发器

一：触发器的定义

    触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦被定义，触发器会被自动的保存在数据库服务器中。任何用户对表的增删改操作均由服务器自动激活相应的触发器。

二：创建触发器

  1. 触发器又叫做事件——条件——动作规则，当定义的特殊事件发生时，会对规则的条件进行检查，条件成立执行触发器中的触发体，不成立则不执行。

     创建形式：

delimiter //-- 将MySQL的结束标志改为//这样遇到;语句不会结束
create trigger <触发器名>
    {before/after} <触发事件:如insert、update等> on <表名>
    for each row/statement
    begin
     <条件+触发体>
    end //
    delimiter ;

注意：不同版本的MySQL定义时语句不一样且不兼容，使用前需自己查看定义语句

  2.old和new的使用

在触发器的触发体中写SQL语句时需要使用到old和new这两个关键字，但在不同触发事件下的使用情况不一样：

特性	INSERT	UPDATE	DELETE
old	NULL	有效	有效
new	有效	有效	NULL

上表中NULL指的是在该触发器条件下不能使用old或者new

三：实战

教学数据库JXDB4的关系模式如下：

SB(Sno,Sname,Age,Sex)

SCB(Sno,Cno,Grade)

CB(Cno,Cname,Teacher)

针对上述教学数据库JXDB4，使用Mysql语句完成以下触发器操作：

（1））新建一个YONGSTUDENT表，数据表结构同SB表。创建触发器trigger_S，用于监视S表上的UPDATE操作，判断其更新后的元组是否有2003年1月1日（含）以后出生的学生，将这样的学生插入YONGSTUDENT表中。

（2）创建触发器trigger_C，当插入课程记录时，要求限制每位教师最多教授3门课程，超过则插入失败；

（3）新建一个SC_History表，用于记录选课成绩表的更新历史记录，包括Sno,Cno,OldGrade(旧成绩),Newgrade(新成绩),Datetime(操作时间)等。创建触发器trigger_SC，当有修改选课成绩操作时，将学号和课程以及对应更新前的成绩、更新后的成绩和操作时间记录到SC_History表中。   

    在完成题目之前我们先创建JXDB4并插入一些数据进去

drop table if exists jxdb4;
drop table if exists sb,cb,scb;
create table sb(
    sno int primary key comment '学号',
    sname char(30) comment '姓名',
    age int check(age>16 and age<25) comment '年龄',
     sex char(5) comment '性别'
)comment '学生表';
create table cb(
     cno char(30) primary key comment '课程号',
  cname char(40) comment '课程名',
  teacher char(20) comment '教师'
)comment '课程表';
create table if not exists scb(
  sno int comment '学号',
  cno char(30) comment '课程号',
  grade int comment '分数' check(grade>0 and grade<100),
   primary key(sno,cno),
   constraint fk_scb_sno  FOREIGN KEY(sno) REFERENCES sb(sno) on update cascade ,
  constraint fk_scb_cno FOREIGN KEY(cno) REFERENCES cb(cno) on delete cascade
)comment '成绩表';
insert into sb(sno, sname, age, sex) values (1520213310,'胡国',19,'男');
insert into sb(sno, sname, age, sex) values (1520213311,'胡超',20,'男');
insert into sb(sno, sname, age, sex) values (1520213312,'胡超',18,'男');
insert into cb(cno, cname, teacher) values ('13-2','java','王五');
insert into cb(cno, cname, teacher) values ('13-1','离散','李四');
insert into scb(sno, cno, grade) values(1520213310,'13-2',91);
insert into scb(sno, cno, grade) values(1520213311,'13-1',90);
insert into cb(cno, cname, teacher) values ('13-3','语文','王五');

此时我们就已经创建了三个表，并且插入了一些数据。

（一）：

题目一要求我们新建一个表结构与SB表相同：

drop table yongstudent;
create table YONGSTUDENT(
                            sno int primary key comment '学号',
                            sname char(30) comment '姓名',
                            age int  comment '年龄',
                            sex char(5) comment '性别'
);

 接下来定义触发器：

drop trigger if exists insert_yong ;
delimiter //
create trigger insert_yong
after update on sb-- 题目要求是更新后所以这里为AFTER
    for each row
begin
  if(year(now())-new.age>=2003) then
  insert into yongstudent(sno, sname, age, sex) values (new.sno,new.sname,new.age,new.sex);
end if;
end;
 delimiter //

然后如果我们现在更新SB表年龄在2003后则就会插入我们新建的YONGSTUDENT表中

（二）：

题目要求不超过三门，则我们需要按老师分组统计数量，定义如下：

delimiter // -- 修改结束条件，遇到//才认为语句结束
create trigger trigger_c
    before insert on cb
    for each row
    begin
        declare num int;
       select count(cno) into num from cb group by teacher having teacher=new.teacher;
      if(num>=3) then
          signal sqlstate 'HY000' set message_text ='老师课程超过三门';
-- 自己定义的报错如果满足超过三这个条件就会报错并且插入失败
      end if ;
    end //
    delimiter ;

(三）：

第三题数据库教材书上有类似的题目，下面我直接把创建表和触发器代码一起发出：

drop table if exists sc_history;
create table SC_History(
                           sno int comment '学号',
                           cno char(30) comment '课程号',
                           OldGrade int comment'旧成绩',
                           NewGrade int comment '新成绩',
                           Datetime date comment '操作时间'
)comment'选课成绩表更新记录';
drop trigger if exists trigger_SC;
delimiter //
create trigger trigger_SC
    after update on scb
    for each row
    begin
        insert into SC_History(sno, cno, OldGrade, NewGrade, Datetime) values (new.sno,new.cno,old.grade,new.grade,now());
    end //
    delimiter ;