/* MySql版本:mysql-5.0.27 */
/* sys_tests表类型:InnoDB */
/* 我想实现修改表sys_tests中id为001的status字段值时,
同时修改id为002的status字段值 */
/* 创建测试表 */
create table sys_tests
(
id varchar(40) not null,
status varchar(40),
primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* 插入测试数据 */
insert into sys_tests (ID, status) values (\'001\', \'running\');
insert into sys_tests (ID, status) values (\'002\', \'waiting\');
insert into sys_tests (ID, status) values (\'003\', \'inactive\');
insert into sys_tests (ID, status) values (\'004\', \'completed\');
/* 创建测试trigger */
CREATE TRIGGER tr_u_sys_tests
BEFORE UPDATE ON sys_tests
FOR EACH ROW
BEGIN
IF OLD.status = \'running\' AND NEW.status = \'completed\' THEN
UPDATE sys_tests SET status = \'running\' WHERE id = \'002\';
END IF;
END;
/* 进行测试 */
UPDATE sys_tests SET status = \'completed\' WHERE id = \'001\';
/* 执行UPDATE测试后提示错误 */
SQL 执行错误 #1442. 从数据库响应:
Can\'t update table \'sys_tests\' in stored function/trigger
because it is already used by statement
which invoked this stored function/trigger.
--转自