触发器统计记录行数

a表保存记录,b表保存a表当中指定cid的总行数
CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

CREATE TRIGGER `insert_exec` AFTER INSERT ON `a` FOR EACH ROW begin
declare b_id int;
select id into b_id from b where cid = new.cid;
if  b_id  then
update b set num=num+1 where cid=new.cid;
else
insert into b (cid,num) values (new.cid, 1);
end if;
end;

CREATE TRIGGER `delete_exec` BEFORE DELETE ON `a` FOR EACH ROW update b  set num = num - 1 where cid= old.cid;


CREATE TABLE `b` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `num` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;


 


Tagged , . Bookmark the permalink.

Comments are closed.