向mysql写入触发器时
create trigger test
before update on tablename for each row
begin
update tablename set NEW.col = ' ';
end
写入正常，但在更新数据时一直出现：
ERROR 1442 (HY000): Can't update table 'tablename' in stored function/trigger becau
se it is already used by statement which invoked this stored function/trigger
原因是触发器在更新本表时触发了触发器，会导致循环调用。
因此需要在触发器中使用set，而不是update。
before update on tablename for each row
begin
set NEW.col = ' ';
end
直接如上就完事了。
附上最近做项目时的一个完整的触发器结构，以备以后使用：
use hotel;
DROP TRIGGER IF EXISTS `triggerForLevelChange`;
create trigger triggerForLevelChange
BEFORE update on vip
for each row
begin
if new.padmoney>='200' and new.padmoney<='300' then
set NEW.lid='2' ;
ELSEif new.padmoney>='300' and new.padmoney<='400' then
set NEW.lid='3' ;
ELSEif new.padmoney>='400' and new.padmoney<='500' then
set NEW.lid='4' ;
ELSEif new.padmoney>='500' and new.padmoney<='600' then
set NEW.lid='5' ;
ELSEif new.padmoney>='600' then
set NEW.lid='6' ;
end if;
end;
