#1.修改my.conf
# max_sp_recursion_depth = 16
delimiter //
CREATE TRIGGER doc_add AFTER INSERT ON doc_threads
for each row begin
update doc_forums set records=records+1 where fid=new.fid;
call syncfid(new.fid,1);
end;//
delimiter ;
delimiter //
CREATE TRIGGER doc_move AFTER UPDATE ON doc_threads
for each row begin
IF old.fid != new.fid then
update doc_forums set records=records+1 where fid=new.fid;
update doc_forums set records=records-1 where fid=old.fid;
call syncfid(new.fid,1);
call syncfid(old.fid,-1);
end IF;
end;
//
delimiter ;
delimiter //
CREATE TRIGGER doc_del AFTER DELETE ON doc_threads
for each row begin
update doc_forums set records=records-1 where fid=old.fid;
call syncfid(old.fid,-1);
end;//
delimiter ;
delimiter //
CREATE PROCEDURE `syncfid`(infid int,n int)
BEGIN
declare tmpfid int default 0;
SELECT fup into tmpfid from doc_forums where fid=infid;
if tmpfid THEN
update doc_forums set records=records+(n) where fid=tmpfid;
call syncfid(tmpfid,n);
end if;
END; //
delimiter ;