有两张表:
CREATE TABLE `websites` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`name` varchar(255) NOT NULL COMMENT '名称',
`url` varchar(255) NOT NULL DEFAULT '' COMMENT '链接',
`alexa` varchar(10) NOT NULL DEFAULT '' COMMENT '号码',
`country` varchar(255) NOT NULL COMMENT '国家',
`type` tinyint(1) DEFAULT NULL COMMENT '类型',
`stuCount` int(11) NOT NULL DEFAULT '0' COMMENT '数量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`stuId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`websites_id` int(11) NOT NULL COMMENT '外键id',
PRIMARY KEY (`stuId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='学生表';
创建触发器:作用是 当表student新增记录之后,websites表中关联的stuCount数就加1,例如:班级表记录了学生数,学生表添加一条记录后,班级表的学生数就加1
CREATE DEFINER=`root`@`localhost` trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from websites where id=new.websites_id);
update websites set stuCount = c + 1 where id = new.websites_id;
end;
创建之后在表student的对象信息DDL就会看到: