mysql触发器问题

[sunature]

创建表时~需要第一个字段(id_1)自增长~第二个字段(id_2)的值等于第一个字段的值~
create table table_1 (
id_1 int primary key auto_increment,
id_2 int not null
);

为此目的编写触发器
|
create trigger t_table_i_ai AFTER INSERT ON table_1
for each row
BEGIN
UPDATE table_1 SET NEW.id_2 = NEW.id_1;
END
|

之后执行插入语句
insert into table_1 values (null,0);

错误如下~
ERROR 1442 (HY000): Can't update table 'module' in stored function/trigger because it is already used by statement which invoked this stored function/
trigger.

买高手解决~或请高手提出解决此问题更好的办法~

[poemdreamer]

以前用 oracle 写触发器 时， 好象不能操作同一个表的 数据，
不知 mysql 是否也是这样


create trigger t_table_i_ai AFTER INSERT ON table_1
for each row
BEGIN
SET &temp = NEW.id_1;
UPDATE table_1 set id_2 =&temp where id_1 = &temp ;
END

[poemdreamer]

create table table_1 (
id_1 int primary key ,
id_2 int not null
);

create table table_2 (
id_1 int primary key ,
id_2 int not null
);

create trigger t_table_i_ai1
AFTER INSERT ON table_1
for each row
insert into table_2(id_1,id_2) values(NEW.id_1,NEW.id_1);

create trigger t_table_i_ai2
AFTER INSERT ON table_2
for each row
update table_1 set table_1.id_2 = new.id_2
where table_1.id_1 = new.id_1 ;


inser into table_1 values(1,2) ;

本来想用两个表操作一下， 结果也是不行

[skyairmj]

在sqlserver是可以在触发器里面修改同一个表的

但是mysql好像不行

[sunature]

看来只能再建一表了~~~谢谢大家了~有劳了~~~

[sunature]

重建了个表~按poemdreamer的方法已经可以了~谢过了~~~:)