delimiter $$ create trigger tri_ab before insert on ab for each row begin if new.address is not null then update cd set iSum=iSum+1 ; end if; end$$ delimiter ;
谢谢楼上的GG,如果我定义的address是varchar类型的,是不是应该改成 if new.address!="" then ?
我测试了,结果有问题那ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end if' at line 1 ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end$$ delimiter' at line 1
create table ab(address varchar(1000) not null); create table cd(sum int not null); DELIMITER $$DROP PROCEDURE IF EXISTS `test2`.`sp_csdn`$$CREATE PROCEDURE `test2`.`sp_csdn`() BEGIN select count(*) from ab into @cnt;
if @cnt <= 1 then select address from ab into @addr; if @addr is not null or @addr != '' then update bc set `sum` = `sum` + 1; end if; else update bc set `sum` = `sum` + @cnt; end if; END$$DELIMITER ;
是insert时不为空呢.还是update时不为空?
感觉你说得不太清楚.
cd中的sum是update操作;功能是在ab中增加address的同时,cd中的sum自动加1.我觉得这个自动累加的功能应该要用存储过程来实现,还请各位帮忙了,50分那,谢谢了!
create trigger tri_ab before insert
on ab for each row
begin
if new.address is not null then
update cd
set iSum=iSum+1 ;
end if;
end$$
delimiter ;
if new.address!="" then ?
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end$$
delimiter' at line 1
不过这个例子只要一个select就能得到结果,cd表显得有些多余。
create table cd(sum int not null);
DELIMITER $$DROP PROCEDURE IF EXISTS `test2`.`sp_csdn`$$CREATE PROCEDURE `test2`.`sp_csdn`()
BEGIN
select count(*) from ab into @cnt;
if @cnt <= 1 then
select address from ab into @addr;
if @addr is not null or @addr != '' then
update bc set `sum` = `sum` + 1;
end if;
else
update bc set `sum` = `sum` + @cnt;
end if;
END$$DELIMITER ;
但不同的dbms产商都自行扩展.所以很多dbms都支持.详细请看官方手册