Solution for Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this store
来源:互联网 发布:软件测试岗位职责 编辑:程序博客网 时间:2024/04/29 06:04
In recently project, i needed to fix a bug about database update. The best way to solve this problem is create an update trigger, but when finished this trigger desiger, a problem show up which the error description is "Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.". So after searched the answer from website, i got a solution about this issue.The following is a sample to show how to fix this problem.
This is a sample table:
CREATE TABLE `t1`
(`a` char(1) default NULL,`b` smallint(6) default NULL);
insert into t1 values ('y','1');
I have a table t1 which has column a and b, i want column a to be updated "n" when column b=0.Here is the fist version i created:
DELIMITER |
CREATE TRIGGER trigger1 AFTER UPDATE ON t1FOR EACH ROW UPDATE t1 SET a= 'n' WHERE b=0;
|DELIMITER ;
After executed this trigger succesfully, i got an error when i start to update this table.The error statement is "Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.".
The following trigger code is a good way:
drop trigger trigger1;
DELIMITER |
CREATE TRIGGER trigger1
BEFORE UPDATE ON t1
FOR EACH ROW
BEGINIF NEW.b=0 THENSET NEW.a = 'n';
END IF;END
|DELIMITER ;
Now you can update the table t1.After got this solution i use it on my project.This is the update trigger i wrote:
DELIMITER $$
DROP TRIGGER ModifyLastUpdated
create trigger ModifyLastUpdated BEFORE UPDATE on EMPLOYEE_SKILL for each row
BEGINif
old.eid=new.eid and old.skill_id=new.skill_id
thenset new.last_updated=current_timestamp;
end if;
END;
$$DELIMITER ;
- Solution for Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this store
- Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/t
- MySQL触发器更新本表数据异常:Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this
- Can't update table 'psxt_user' in stored function/trigger because it is already used by statement wh
- ERROR 1442 (HY000): Can\'t update table \'sign\' in stored function/trigger because it is already used by statement which invoke
- mysql -- error code [1442]; Can't update table 'table_name' in stored function/trigger because it is already used by statement w
- mysql 触发器的问题 Can't update table 'tbl' in stored function/trigger because it is already used by state
- MySQL触发器更新本表数据异常:Can’t update table ‘tbl’ in stored function/trigger because it is already used by s
- mysql创建触发器错误提示:Can not update in stored function/trigger already used by statement invoked
- 错误it is already used by statement which invoked this stored functiontrigger的解决方法
- 触发器在插入时更新此条新记录 错误it is already used by statement which invoked this stored functiontrigger的解决方法
- 创建触发器后不能进行修改操作,Can't update table 'psxt_user' in stored function/trigger because
- Can't perform Create, Update or Delete operations on 'Table(@TableName)' because it is read-only.
- Can't install '*' from pristine store, because no checksum is recorded for this file
- The Simulator can't be launched because it is already in use.
- Xcode The Simulator can't be launched because it is already in use .
- The Simulator can't be launched because it is already in use.
- Xcode The Simulator can't be launched because it is already in use .
- JS获取表格行数和列数
- php的一些小技巧
- 容器 小结
- 使用Hibernate向MySQL数据库中存取文件
- 节后综合症
- Solution for Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this store
- URL Rewrite(4):不同级别URL Rewrite的一些细节与特点
- 常用Windows 运行命令
- Windows 内存泄漏检测工具——LeakDiag
- ideas about sharing software
- 期待Microsoft的“群体搜索”(groupization)
- 关于java 读取propterties 文件的疑惑 和问题的解决
- 一个Action写多个方法
- 2009,本命年的期望