mysql學(xué)習(xí)之觸發(fā)器
在借閱表和讀者表當(dāng)中存在著這樣的關(guān)系,如果在借閱表當(dāng)中添加一條數(shù)據(jù),讀者表當(dāng)中對(duì)應(yīng)的累計(jì)借書字段就自增1,如果在借閱表當(dāng)中刪除一條數(shù)據(jù),讀者表當(dāng)中對(duì)應(yīng)的累計(jì)借書字段就自減1,實(shí)現(xiàn)本功能的方法如下。
1、創(chuàng)建讀者表
1 CREATE TABLE `t_borrowbook` ( 2 `borrowBookId` int(11) NOT NULL AUTO_INCREMENT, 3 `bookId` int(11) DEFAULT NULL, 4 `id` int(11) DEFAULT NULL, 5 `audienceId` int(11) DEFAULT NULL, 6 `startTime` datetime DEFAULT NULL, 7 `endTime` datetime DEFAULT NULL, 8 `borrowBookDesc` text, 9 PRIMARY KEY (`borrowBookId`) 10 )
2、創(chuàng)建借閱表
1 CREATE TABLE `t_audience` ( 2 `audienceId` int(10) NOT NULL AUTO_INCREMENT, 3 `audienceName` varchar(20) NOT NULL, 4 `audienceTypeId` int(10) NOT NULL, 5 `audienceNumber` varchar(18) NOT NULL, 6 `sex` varchar(20) DEFAULT NULL, 7 `cellphone` varchar(11) DEFAULT NULL, 8 `borrowBookNum` int(10) DEFAULT NULL, 9 `audienceDesc` text, 10 PRIMARY KEY (`audienceId`) 11 )
3、創(chuàng)建插入自增觸發(fā)器
1 DELIMITER $$ 2 3 CREATE 4 /*[DEFINER = { user | CURRENT_USER }]*/ 5 TRIGGER `db_book`.`Tr_borrowbook_Insert` AFTER INSERT 6 ON `db_book`.`t_borrowbook` 7 FOR EACH ROW BEGIN 8 UPDATE t_audience SET borrowBookNum = borrowBookNum+1 WHERE audienceId=new.audienceId; 9 END$$ 10 11 DELIMITER ;
4、創(chuàng)建刪除自減觸發(fā)器
1 DELIMITER $$ 2 3 CREATE 4 /*[DEFINER = { user | CURRENT_USER }]*/ 5 TRIGGER `db_book`.`Tr_borrowbook_Delete` AFTER DELETE 6 ON `db_book`.`t_borrowbook` 7 FOR EACH ROW BEGIN 8 UPDATE t_audience SET borrowBookNum = borrowBookNum-1 WHERE audienceId=old.audienceId; 9 END$$ 10 11 DELIMITER ;
每天進(jìn)步一點(diǎn)點(diǎn),一切都是最好的安排。
posted on 2016-12-20 23:10 啊遠(yuǎn) 閱讀(503) 評(píng)論(0) 收藏 舉報(bào)
浙公網(wǎng)安備 33010602011771號(hào)