在借阅表和读者表当中存在着这样的关系,如果在借阅表当中添加一条数据,读者表当中对应的累计借书字段就自增1,如果在借阅表当中删除一条数据,读者表当中对应的累计借书字段就自减1,实现本功能的方法如下。
1、创建读者表
CREATE TABLE `t_borrowbook` (
`borrowBookId` int(11) NOT NULL AUTO_INCREMENT,
`bookId` int(11) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`audienceId` int(11) DEFAULT NULL,
`startTime` datetime DEFAULT NULL,
`endTime` datetime DEFAULT NULL,
`borrowBookDesc` text,
PRIMARY KEY (`borrowBookId`)
)
2、创建借阅表
CREATE TABLE `t_audience` (
`audienceId` int(10) NOT NULL AUTO_INCREMENT,
`audienceName` varchar(20) NOT NULL,
`audienceTypeId` int(10) NOT NULL,
`audienceNumber` varchar(18) NOT NULL,
`sex` varchar(20) DEFAULT NULL,
`cellphone` varchar(11) DEFAULT NULL,
`borrowBookNum` int(10) DEFAULT NULL,
`audienceDesc` text,
PRIMARY KEY (`audienceId`)
)
3、创建插入自增触发器
DELIMITER $$ CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `db_book`.`Tr_borrowbook_Insert` AFTER INSERT
ON `db_book`.`t_borrowbook`
FOR EACH ROW BEGIN
UPDATE t_audience SET borrowBookNum = borrowBookNum+1 WHERE audienceId=new.audienceId;
END$$ DELIMITER ;
4、创建删除自减触发器
DELIMITER $$ CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `db_book`.`Tr_borrowbook_Delete` AFTER DELETE
ON `db_book`.`t_borrowbook`
FOR EACH ROW BEGIN
UPDATE t_audience SET borrowBookNum = borrowBookNum-1 WHERE audienceId=old.audienceId;
END$$ DELIMITER ;
每天进步一点点,一切都是最好的安排。