刚接触触发器,下面这个折腾一下午老报错。麻烦看下问题出在哪。
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写的不对?
解决方案 »
- mysql 数据导入出错 如何全部回滚
- 如何将txt文件导入Mysql?
- federeate引擎大表创建成功不能出数据
- 是否有必要打开 query_cache_type
- 数据库查询问题?
- mysql如何提高数据库的排序效率?
- mysql不支持中文全文索引,你在建站中是怎么解决全文搜索的?
- 请问怎么样对mysql group by后每组进行排序呢
- 怎么样根据生日选择年龄(段)mysql TO_DAYS(NOW()) - TO_DAYS(date_col)<= 30返回天数 怎样返回年
- 如何配置MYSQL在REDHAT7。1上?
- 触发器是否能够实现对新写入的记录马上update他的新字段的值
- 数据表设计讨论
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.