第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
