我要建立一个触发器,要求将v_studentName表中v_teacherID设为10
下面是manager表和错误信息
http://hi.baidu.com/w511170721/album/item/14f3e8894aaff0b9a5c27211.html
http://hi.baidu.com/w511170721/album/item/52b10bfb545d0ca49f514611.html
下面是manager表和错误信息
http://hi.baidu.com/w511170721/album/item/14f3e8894aaff0b9a5c27211.html
http://hi.baidu.com/w511170721/album/item/52b10bfb545d0ca49f514611.html
我确定表里面有姓王的人
create trigger 语句。然后你的问题是什么?这样得到答复的机会会多一些。
create table manager(v_teacherID varchar(50),v_teacherName varchar(50),v_schoolAddress varchar(50),v_schoolTime datetime,v_courseID varchar(50),v_courseName varchar(50),v_studentID varchar(50) primary key,v_studentName varchar(50),v_taskSize int,v_hostIP varchar(15),v_hostMac varchar(17),v_hostName varchar(50);
测试数据,其他字段均为空:
insert into manager(v_studentID,v_studentName,b_hostState) values
'20606032011','高阳',0),('20606032012','桂安军',0),('20606032013','郭健',0),('20606032014','何群明',0),('20606032015','somebody',0'20606032026','somebody',0),('20606032027','刘亚亚',0),('20606032028','somebody',0),('20606032029','somebody',0),('20606032030','齐少',0),('20606032031','somebody',0),('20606032032','苏本友',0),('20606032033','孙彬',0),('20606032034','孙小柱',0),('20606032035','汪超',0),('20606032036','王冠',0),('20606032037','王凯',0)触发器及错误显示:
mysql> create trigger manager_bi before insert on manager for each row
-> set new.v_teacherID=10
-> where v_studentName like '王%';
ERROR 1064 (42000): 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 'where
v_studentName like '王%'' at line 3
如果是想改当前插入的这条记录,则
if new.v_studentName like like '王%' then
set new.v_teacherID=10 ;
end if2. 如果是想把表中已经记录更新 update manager set v_teacherID=10 where v_studentName like '王%'; 则触发器中无法对自身表进行这种操作。
mysql> create trigger manager_bi before insert on manager
-> for each row if new.v_studentName like '王%' then
-> set new.v_teacherID=10
-> end if;
ERROR 1064 (42000): 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 i
f' at line 4
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.htmldelimiter |create trigger manager_bi before insert on manager
for each row
BEGIN
if new.v_studentName like '王%' then
set new.v_teacherID=10;
end if;
END;delimiter ;
触发器及错误显示:
mysql> create trigger manager_bi before insert on manager for each row
-> set new.v_teacherID=10
-> where v_studentName like '王%';
ERROR 1064 (42000): 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 'where
v_studentName like '王%'' at line 3
[/Quote]报的错误那么明显了
很基本的语法错误啊,建议多参考下mysql官方手册吧