第9章 触发器
入的新数据放到new表，删除的数据放到old表。
准备本章学习环境
连接数据库schoolDB，删除表TStudent，TScore和Tsubject中的所有数据。
delete from TStudent;
delete from TScore;
delete from TSubject;
向学生表插入两条记录
insert TStudent (StudentID,Sname,sex,Class)
values ('00001','张作霖','男','JAVA'),
('00002','汤二虎','男','NET')
向课程表插入3条记录
insert into TSubject values
('0001','计算机网络','奠基计算机网络','清华出版社'),
('0002','数据结构','大话数据结构','人邮出版社'),
('0003','JAVA开发','JAVA企业级开发','人邮出版社')
创建插入触发器
1. 练习：创建插入触发器
查看表TStudent中的记录，你发现没有录入时间enterTime，也没有邮箱Email。
select * from TStudent
下面创建触发器，在TStudent表中插入记录时，使用触发器插入录入时间即enterTime和邮箱即Email。
创建触发器
CREATE TRIGGER autoTimeAndEmail
BEFORE INSERT on `TStudent`
FOR EACH ROW
BEGIN
SET NEW.enterTime=NOW();
SET NEW.Email=concat(PINYIN(NEW.sname),'@hotmail.com');
END
插入两条记录测试触发器是否工作。
insert TStudent (StudentID,Sname,sex,Class)
values ('00003','张左相','男','JAVA'),
('00004','张四非','男','NET')
查看新插入的记录，发现已经由触发器插入了用户邮箱和录入时间
select * from TStudent
2. 练习：使用触发器实现数据插入跟踪
使用触发器实现对TStudent表数据插入的跟踪，将跟踪事件记录到一张审计表中review。
一张表不能同时有多个插入触发器
Drop TRIGGER autoTimeAndEmail
创建记录跟踪的审计表
create table review
(
username varchar(20),
act VARCHAR(10),
studentID varchar(10),
sname VARCHAR(10),
actTime TIMESTAMP
)
创建触发器，该触发器向insertReview表中记录
CREATE TRIGGER insertReview BEFORE INSERT on `TStudent`
FOR EACH ROW
BEGIN
insert review values (user(),'insert',NEW.studentID,NEW.sname,NOW());
END
在Tstudent表插入一条记录
INSERT `TStudent` (studentid,sname,sex,class) VALUES ('00005','王严明','男','NET')
查看review表是否记录了
select * from review
可以看到已经记录下那个用户什么时间插入了一条记录
3. 练习：创建update触发器
在TStudent表上创建触发器，在review表中记录更改学生的学号和更改前的姓名。
CREATE TRIGGER updateReview BEFORE UPDATE on `TStudent`
FOR EACH ROW
BEGIN
insert review values (user(),'update',NEW.studentID,old.sname,NOW());
END
查看学号是00005的学生姓名
select * from `TStudent` where studentID='00005'
UPDATE `TStudent` SET sname='冯国彰' where studentID='00005'
查看是否记录
select * from `review`
4. 练习：创建删除触发器
该触发器能够记录TStudent表的删除事件到review表
CREATE TRIGGER deleteReview BEFORE DELETE on `TStudent`
FOR EACH ROW
BEGIN
insert review values (user(),'delete',old.studentID,old.sname,NOW());
END
删除二条记录
delete from `TStudent` where studentID='00002'
select * from review
5. 练习：在触发器中使用异常回滚操作
MySQL的触发器中不支持rollback和 commit，也就是说如果出现异常，没有办法回滚。解决办法是，如果打算回滚更改，人为产生异常。
创建触发器，限定TStudent表sex列只能输入‘男’或‘女’。如果是其他值，取消插入操作。
注意红色部分是故意产生的错误，因为没有定义E001，这样会自动取消插入。
CREATE TRIGGER limitSex BEFORE INSERT on `TStudent`
FOR EACH ROW
BEGIN
declare M_ERRMSG varchar(10);
if NEW.sex='男' or NEW.sex='女' then
set M_ERRMSG='插入成功';
else select E001 INTO M_ERRMSG;
end if;
END
插入记录，其中一条性别是错误的
insert TStudent (StudentID,Sname,sex,Class)
values ('00005','冯国章','男','JAVA'),
('00006','牛万鹏','南','NET')
可以看到插入失败
查看是否插入了两条，可以看到一条也没插入成功。
直插入一条正确的
insert TStudent (StudentID,Sname,sex,Class) values ('00005','冯国章','男','JAVA')
可以看到插入成功
6. 练习：查看创建的触发器
双击表，在Triggers标签下，可以看到表上的触发器
在以下图中也可以看到创建的触发器
7. 练习：查看触发器的定义
8. 练习：使用SHOW TRIGGERS显示触发器;
SHOW TRIGGERS;
9. 练习：查看所有数据定义的触发器
连接到Information_schema数据库，输入以下命令查看所有触发器
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
10. 练习：删除触发器
删除触发器，触发器名字区分大小写。
drop trigger limitSex
思考题
1. 在Tstudent表上创建触发器在8点到9点之间才能进行update操作，其他时间段不允许进行更改。
广告
