刚接触触发器,下面这个折腾一下午老报错。麻烦看下问题出在哪。
create trigger `tri_right` before insert on `t_userright`
for each row begin
if NEW.right_type = '0' then
update `t_userright` set sts = '1',sts_date = now()
where worker_id = new.worker_Id and right_type = '0' and sts = '0';
end if ;
end ;报错:
Script line: 1 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 '' at line 5
Script line: 6 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
Script line: 7 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' at line 1我的版本是 5.0.51b写成这样不报错。
create trigger `tri_right` before insert on `t_userright`
for each row
update `t_userright` set sts = '1',sts_date = now()
where worker_id = new.worker_Id and right_type = '0' and sts = '0';
是我的if写的不对?
create trigger `tri_right` before insert on `t_userright`
for each row begin
if NEW.right_type = '0' then
update `t_userright` set sts = '1',sts_date = now()
where worker_id = new.worker_Id and right_type = '0' and sts = '0';
end if ;
end ;报错:
Script line: 1 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 '' at line 5
Script line: 6 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
Script line: 7 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' at line 1我的版本是 5.0.51b写成这样不报错。
create trigger `tri_right` before insert on `t_userright`
for each row
update `t_userright` set sts = '1',sts_date = now()
where worker_id = new.worker_Id and right_type = '0' and sts = '0';
是我的if写的不对?
delimiter /
create trigger `tri_right` before insert on `t_userright`
for each row begin
if NEW.right_type = '0' then
update `t_userright` set sts = '1',sts_date = now()
where worker_id = new.worker_Id and right_type = '0' and sts = '0';
end if ;
end ;
/
delimiter ;
#1442 - Can't update table 't_userright' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 我是要实现,再给这个表插入数据时,把表里和新纪录同一个worker_id的行 的状态改成1.