我有一张表sns_member里面有mobile,wathes两个字段,前一个是手机号,后一个是数量。还有一张表sns_member_watch,是里面有mobile,content,然后我想把member_watch表里面对应的moible的数量更新到member表的watches上去Create TRIGGER `memberTri`
AFTER insert
ON `sns_member`
FOR EACH ROW
BEGIN
DECLARE m INT;
declare cur Cursor for select mobile from sns_member;
open cur;
REPEAT
fetch cur INTO m;
declare @watchMembers int ;
set @watchMembers=(select count(*) from sns_watch_member where mobile=m);
UPDATE sns_member SET watches = @watchMembers WHERE mobile = m;
END REPEAT;
close cur;
END;trigger
AFTER insert
ON `sns_member`
FOR EACH ROW
BEGIN
DECLARE m INT;
declare cur Cursor for select mobile from sns_member;
open cur;
REPEAT
fetch cur INTO m;
declare @watchMembers int ;
set @watchMembers=(select count(*) from sns_watch_member where mobile=m);
UPDATE sns_member SET watches = @watchMembers WHERE mobile = m;
END REPEAT;
close cur;
END;trigger
AFTER insert,update,delete
ON `sns_member_watch`
FOR EACH ROW
BEGIN
DECLARE m INT;
declare cur Cursor for select mobile from sns_member;
open cur;
REPEAT
fetch cur INTO m;
declare @watchMembers int ;
set @watchMembers=(select count(*) from sns_watch_member where mobile=m);
UPDATE sns_member SET watches = @watchMembers WHERE mobile = m;
END REPEAT;
close cur;
END;
Create TRIGGER `memberTri`
AFTER insert
ON `sns_member_watch`
FOR EACH ROW
BEGIN
DECLARE m INT;
declare cur Cursor for select mobile from sns_member;
open cur;
REPEAT
fetch cur INTO m;
declare @watchMembers int ;
set @watchMembers=(select count(*) from sns_watch_member where mobile=m);
UPDATE sns_member SET watches = @watchMembers WHERE mobile = m;
END REPEAT;
close cur;
END;运行后好像 提示这一句有错误哎set @watchMembers=(select count(*) from sns_watch_member where mobile=m);
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
select count(*) into @watchMembers from sns_watch_member where mobile=m;
在UPDATE语句后面
SET done = 0;
AFTER insert
ON `sns_member`
FOR EACH ROW
BEGIN
DECLARE m INT;
DECLARE done INT DEFAULT 0;
declare cur Cursor for select mobile from sns_member ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur;
while done=0 do
fetch cur INTO m;
UPDATE sns_member SET watch_members = watch_members+1 WHERE mobile = m;
set done=0
END while;
close cur;
END;
while done=0 do
UPDATE sns_member SET watch_members = watch_members+1 WHERE mobile = m;
set done=0
Fetch cur INTO m;
END while;
Create TRIGGER `memberTri`
AFTER insert
ON `sns_member_watch`
FOR EACH ROW
BEGIN
UPDATE sns_member SET watch_members = watch_members+1 WHERE mobile =NEW.mobile NEW.from_user_name;
END
当sns_member_watch有数据时,就把对应的mobile所在的行的数据加1
有没有通过语句将它赋值为1的
DECLARE done INT DEFAULT 0;
declare c INT;
declare cur Cursor for select watch_members from sns_member where mobile=NEW.from_user_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur;
while done=0 do
fetch cur INTO c;
if c=NULL then
UPDATE sns_member SET watch_members = 1 WHERE mobile = NEW.from_user_name;
else
UPDATE sns_member SET watch_members = watch_members+1 WHERE mobile = NEW.from_user_name;
end if;
end while;
close cur;
END