1.觸發(fā)器定義
同存儲(chǔ)過程和函數(shù)類似，MySQL中的觸發(fā)器也是存儲(chǔ)在系統(tǒng)內(nèi)部的一段程序代碼，可以把它看作是一個(gè)特殊的存儲(chǔ)過程。所不同的是，觸發(fā)器無需人工調(diào)用，當(dāng)程序滿足定義條件時(shí)就會(huì)被MySQL自動(dòng)調(diào)用。這些條件可以稱為觸發(fā)事件，包括INSERT、UPDATE和DELETE操作。
2. 創(chuàng)建觸發(fā)器語法
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW
trigger_body
trigger_time：觸發(fā)器觸發(fā)時(shí)機(jī)，有before和after
trigger_event：觸發(fā)器觸發(fā)事件，有insert,update,delete三種
trigger_body：觸發(fā)器主體語句
從MySQL5.7開始，可以為一張表定義具有相同觸發(fā)事件和觸發(fā)時(shí)機(jī)的多個(gè)觸發(fā)器。默認(rèn)情況下，具有相同觸發(fā)事件和觸發(fā)時(shí)機(jī)的觸發(fā)器按其創(chuàng)建順序激活。
3. 觸發(fā)器
3.1 after觸發(fā)器
AFTER觸發(fā)器是指觸發(fā)器監(jiān)視的觸發(fā)事件執(zhí)行之后，再激活觸發(fā)器，激活后所執(zhí)行的操作無法影響觸發(fā)器所監(jiān)視的事件。
3.1.1 delete觸發(fā)器
建一個(gè)觸發(fā)器t_d_s，當(dāng)刪除表student中某個(gè)學(xué)生的信息，同時(shí)將grade表中與該學(xué)生有關(guān)的數(shù)據(jù)全部刪除。
CREATE TRIGGER trigger_t1
AFTER DELETE ON student
FOR EACH ROW
BEGIN
DELETE FROM grade WHERE studentid = old.studentid;
END
3.1.2 new和old
觸發(fā)器不會(huì)產(chǎn)生new表和old表，所謂new，old只是指insert,delete,update操作執(zhí)行前的所在表狀態(tài)和執(zhí)行后的狀態(tài)
對(duì)insert而言，只有new合法，新插入的行用new來表示，行中每一列的值用new.列名來表示
對(duì)于delete而言，只有old合法，刪除的行用old來表示，行中每一列的值用old.列名來表示
對(duì)于update而言，被修改的行，修改前的數(shù)據(jù)，用old來表示，old.列名；修改后的數(shù)據(jù)，用new來表示，new.列名
3.1.3 UPDATE
創(chuàng)建一觸發(fā)器t_u_s，實(shí)現(xiàn)在更新學(xué)生表的學(xué)號(hào)時(shí)，同時(shí)更新grade表中的相關(guān)記錄的studentid值。
CREATE TRIGGER t_u_s
AFTER UPDATE ON student
for EACH ROW
BEGIN
UPDATE grade SET studentid = new.studentid WHERE studentid = old.studentid;
END
3.1.4 INSERT
- 創(chuàng)建一個(gè)存儲(chǔ)過程，根據(jù)student表中數(shù)據(jù)，一次性更新class表中每個(gè)班的人數(shù)
CREATE PROCEDURE p_tao()
BEGIN
DECLARE num int;
DECLARE cid VARCHAR(20);
DECLARE done boolean DEFAULT true;
DECLARE cur CURSOR FOR
SELECT classid,COUNT(*)
FROM student
GROUP BY classid;
DECLARE CONTINUE HANDLER FOR NOT found SET done = false;
UPDATE class set studentnum = 0;
OPEN cur;
FETCH cur INTO cid,num;
WHILE done DO
UPDATE class SET StudentNum = num WHERE classid = cid;
FETCH cur INTO cid,num;
END WHILE;
CLOSE cur;
END
CALL p_tao();
未調(diào)用存儲(chǔ)過程前：
調(diào)用存儲(chǔ)過程后：
?
4.2 before觸發(fā)器
BEFORE觸發(fā)器是指觸發(fā)器在所監(jiān)視的觸發(fā)事件執(zhí)行之前激活，激活后執(zhí)行的操作先于監(jiān)視的事件，這樣就有機(jī)會(huì)進(jìn)行一些判斷，或修改即將發(fā)生的操作。
Before與After區(qū)別：
before：(insert、update)可以對(duì)new進(jìn)行修改，after不能對(duì)new進(jìn)行修改，三者都不能修改old數(shù)據(jù)。
4.2.1 INSERT
給teacher表創(chuàng)建一個(gè)列， salary列，記錄教師的工資
建一個(gè)觸發(fā)器t_d_t，插入教師信息時(shí)，如果教師工資小于3000，則自動(dòng)調(diào)整成3000
#給teacher表創(chuàng)建一個(gè)列， salary列，記錄教師的工資
ALTER table teacher ADD salary int;
#建一個(gè)觸發(fā)器tdt，插入教師信息時(shí)，如果教師工資小于3000，則自動(dòng)調(diào)整成3000
CREATE TRIGGER tdt
BEFORE INSERT
ON teacher
for each ROW
BEGIN
if new.salary <3000 THEN SET new.salary = 3000;
END if;
END;
INSERT INTO teacher(TeacherID,Teachername,sex,salary) VALUES('123','位老師','女',2999);
INSERT INTO teacher(TeacherID,Teachername,sex,salary) VALUES('124','文老師','男',3001);
4.2.2 UPDATE
給grade表建立一個(gè)學(xué)分列，并創(chuàng)建一個(gè)觸發(fā)器，當(dāng)修改grade表中數(shù)據(jù)時(shí)，如果修改后的成績小于60分，則觸發(fā)器將該成績對(duì)應(yīng)的課程學(xué)分修改為0，否則將學(xué)分改成對(duì)應(yīng)課程的學(xué)分
ALTER TABLE grade ADD credit int;
CREATE TRIGGER trigger_ch
BEFORE UPDATE
ON grade
FOR EACH ROW
BEGIN
IF new.grade<60 THEN SET new.credit = 0;
ELSE SET new.credit = (
SELECT credit
FROM coure
WHERE courseid = new.courseid
);
END if;
END
UPDATE grade SET grade = 50 WHERE courseid ="Dp010001" AND studentid = "St0109010003"
5. 中斷觸發(fā)器
假設(shè)軟件B1802班級(jí)最多只能有4個(gè)人，當(dāng)往b_student表中增加新生信息時(shí)，b_class班級(jí)表內(nèi)學(xué)生人數(shù)會(huì)隨之增加，當(dāng)人數(shù)大于4人時(shí)，由于超過人數(shù)限制，會(huì)報(bào)系統(tǒng)錯(cuò)誤，錯(cuò)誤提示為“超過人數(shù)限制”，并且該觸發(fā)器所有操作（包括引發(fā)觸發(fā)器的操作）均不能成功。
#創(chuàng)建b_classs表存放班級(jí)人數(shù)，如果班級(jí)人數(shù)大于4就提示"超出人數(shù)限制"
CREATE TABLE B_class(
Cid VARCHAR(20) PRIMARY KEY COMMENT "班級(jí)名稱",
num int COMMENT "人數(shù)"
);
#插入初始數(shù)值，初始數(shù)值軟件B1802班沒有人
INSERT INTO b_class VALUES("軟件B1802",0);
#創(chuàng)建一個(gè)b_student表存放學(xué)生信息
CREATE TABLE b_student(
studentid VARCHAR(20) PRIMARY KEY COMMENT "學(xué)號(hào)",
studentname VARCHAR(20) not null COMMENT "姓名",
classid VARCHAR(20) DEFAULT '軟件B1802' COMMENT "班級(jí)",
CONSTRAINT FK_ID FOREIGN KEY(classid) REFERENCES B_class(Cid)
ON DELETE RESTRICT on UPDATE CASCADE
)DEFAULT CHARSET = utf8;
#要注意的是，我們?cè)诓迦雽W(xué)生信息的時(shí)候，我們要使用觸發(fā)器來更新班級(jí)表中的人數(shù)，不然盡管你在學(xué)生信息表插入多條數(shù)據(jù)，班級(jí)表中的人數(shù)會(huì)一直保持不表，也就不會(huì)出現(xiàn)超出人數(shù)限制的情況。
CREATE TRIGGER add_trigger
AFTER INSERT
ON b_student
for EACH ROW
BEGIN
UPDATE b_class SET num = num + 1 WHERE Cid = new.classid;
END;
#創(chuàng)建拋出自定義異常的觸發(fā)器，當(dāng)插入學(xué)生人數(shù)超過4人時(shí)，就會(huì)拋出異常。
CREATE TRIGGER exception BEFORE INSERT ON b_student for each row
BEGIN
DECLARE number int;
SELECT num INTO number from b_class WHERE cid = new.classid;
if number = 4 THEN SIGNAL SQLSTATE '45000'
SET message_text = '超出人數(shù)限制',MYSQL_ERRNO = 1333;
END if;
END;
INSERT INTO b_student(studentid,studentname) VALUES('238','位傲氣'),('239','阮氏問'),('240','王隴鎮(zhèn)'),('250','周志豪');
INSERT INTO b_student(studentid,studentname) VALUES('241','劉洋');
插入學(xué)生信息之前：
插入學(xué)生信息之后：
?
6. 查看觸發(fā)器
在MySQL5.7以前，對(duì)同一個(gè)表相同觸發(fā)時(shí)機(jī)的相同觸發(fā)事件，只能定義一個(gè)觸發(fā)器。例如，對(duì)于某個(gè)表的不同字段的AFTER更新觸發(fā)器，只能定義成一個(gè)觸發(fā)器，在觸發(fā)器中通過判斷更新的字段進(jìn)行相應(yīng)的處理。所以在創(chuàng)建觸發(fā)器之前，最好能夠查看MySQL中是否已經(jīng)存在該觸發(fā)器。
MySQL中，查看觸發(fā)器有兩種方法，一種是使用SHOW TRIGGERS語句，一種是SHOW CREATE TRIGGERS TRIGGERNAME 查看觸發(fā)器的詳細(xì)信息。
7. 刪除觸發(fā)器
使用DROP TRIGGER語句可以刪除MySQL中定義的觸發(fā)器，基本語法形式如下：
DROP TRIGGER trigger_name
文章來源：http://www.zghlxwxcb.cn/news/detail-763053.html
?文章來源地址http://www.zghlxwxcb.cn/news/detail-763053.html
到了這里，關(guān)于MySQL進(jìn)階——觸發(fā)器的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
