第12节-MySQL触发器

12.1、触发器介绍

触发器是一种特殊的存储过程，只要满足一定条件，对数据表进行INSERT、UPDATE、DELETE操作时，数据库系统就会自动执行触发器中定义的程序语句，以进行维护数据完整性或其他一些特殊的任务。

触发器是MYSQL的数据库对象之一，与存储过程非常相似，都需要声明﹑执行等。但是触发器的执行不是由程序调用的，也不是手工启动的，而是由事件来触发的。

MySQL提供了两个逻辑表NEW和OLD,NEW和OLD的表结构与触发器所在数据表的结构完全一致，当触发器的执行完成之后，这两个表也会被自动删除。

OLD表用来存放更新前的记录:
对于UPDATE语句,OLD表中存放的是更新前的记录（更新完后即被删除);
对于DELETE语句,该表中存放的是被删除的记录。

NEW表用来存放更新后的记录:
对于INSERT语句,NEW表中存放的是要插入的记录;
对于UPDATE语句，该表中存放的是要更新的记录。

12.2、测试的表结构

CREATE TABLE `student` (
  `sno` varchar(20) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `ssex` enum('男','女','') DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sdept` varchar(20) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `score` (
  `sno` varchar(20) DEFAULT NULL,
  `courseid` int(11) DEFAULT NULL,
  `grade` tinyint(4) DEFAULT NULL,
  KEY `fk_courseid` (`courseid`),
  KEY `fk_snoid` (`sno`),
  CONSTRAINT `fk_courseid` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`),
  CONSTRAINT `fk_snoid` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

12.3、触发器管理

12.3.1、创建触发器

12.3.1.0、注意事项

请注意:
若有外键约束,这个更新语句执行不成功。
可以通过以下语句让外键约束暂时失效:SET FOREIGN_KEY_CHECKS=0;

12.3.1.1、update触发器

-- 1、update，创建触发器，当sno更新时，则更新score表
-- after:更新之后执行，before:更新之前执行
delimiter $$
create trigger sno_update after update on student for each row
begin
    update score set sno=new.sno where sno=old.sno;
end$$
delimiter ;

-- 更新时，查看触发器,是否生效
update student set sno='99999' where sname='洛燕妮';

12.3.1.2、insert触发器

-- 2、delete,创建触发器，当学生被旧除时，同步删除score表该学生数据
delimiter $$
create trigger sno_delete after delete on student for each row
begin
delete from score where sno=old.sno;
end$$
delimiter ;

-- 测试触发器是否生效
delete from student where sno='99999';

12.3.1.3、delete触发器

-- 3、insert,创建触发器，插入数据时，sno自动+1
delimiter $$
create trigger trig_auto before insert on student for each row
begin
    declare index_sno int;
    select max(sno) into index_sno from student;
    set new.sno=index_sno+1;
end$$
delimiter ;

-- 插入数据，检查是否有更新
insert into student values ('123','ok','男',88,'计算机系','2000-01-03 13:12:12');

12.3.2、查看触发器

show triggers;

12.3.3、删除触发器

drop trigger trig_auto;

12.4、实战

12.4.1、实战1【阻止更新某列的数据】

delimiter $$
create trigger sno_update before update on student for each row
begin
    set new.sno=old.sno;
end$$
delimiter ;

12.4.2、实战2【阻止插入数据】

delimiter $$
create trigger check_student before insert on student for each row
begin
declare vcount int;
    select count(ssex) into vcount from student where ssex='男';
    if vcount>=8 then
    signal sqlstate '45000' set message_text='不允许插入';
    end if;
end$$
delimiter ;