Mysql trigger 问题

Question

有表
CREATE TABLE `myarticle` (                                                           
             `ID` int(11) NOT NULL auto_increment,                                              
             `ChannelID` int(11) default NULL,                                                  
             `Title` varchar(250) default NULL,                                                 
             `ViceTitle` varchar(250) default NULL,                                             
             `Content` mediumtext,                                                              
             `Summary` mediumtext,                                                              
             `KeyWord` varchar(250) default NULL,                                               
             `Source` varchar(50) default NULL,                                                 
             `Author` varchar(50) default NULL,                                                 
             `PublishDate` datetime default NULL,                                               
             `ReadTimes` int(11) default NULL,                                                  
             `Score` int(11) default NULL,                                                      
             `PicAccID` int(11) default NULL,                                                   
             `UserId` int(11) default NULL,                                                     
             `State` varchar(50) default '新稿',                                              
             `DoDate` datetime default NULL,                                                    
             `ID2` int(11) NOT NULL default '1',                                                
             PRIMARY KEY  (`ID`)                                                                
           ) ENGINE=InnoDB DEFAULT CHARSET=gbk CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC  
现在想用触发器来实现 当在表中插入一条记录时 使 ID2 的值等于ID 的值，ID 的值是自动增加的。
create trigger `mysqlwebproject`.`mm` AFTER INSERT on `mysqlwebproject`.`myarticle` 
for each row BEGIN
DECLARE aa int ;
set aa= NEW.ID;
update myarticle set myarticle.ID2=aa where myarticle.ID=aa;
END;这个是我自己写的 触发器，当插入数据时报错，错误信息如下
Error Number : 1442 
Error Message: Can't update table 'myarticle' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
我该怎么写啊，请指点

Answer 1

MySQL中不允许在触发器中再来UPDATE本表。你需要的这个功能很难实现。 只能近似的实现。(下面方法受并发的影响)mysql> CREATE TABLE `myarticle` (
    ->        `ID` int(11) NOT NULL auto_increment,
    ->        `ChannelID` int(11) default NULL,
    ->        `ID2` int(11) NOT NULL default '1',
    ->        PRIMARY KEY  (`ID`)
    ->      );
Query OK, 0 rows affected (0.09 sec)mysql> delimiter |
mysql>
mysql> CREATE TRIGGER testref BEFORE INSERT ON myarticle
    ->   FOR EACH ROW BEGIN
    ->          declare aa INT;
    ->          select max(id) into aa from myarticle;
    ->          set NEW.ID2=IFNULL(aa,0)+1;
    ->   END;
    -> |
Query OK, 0 rows affected (0.14 sec)mysql> delimiter ;
mysql>
mysql> insert into myarticle(ChannelID) values (10),(20),(30);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from myarticle;
+----+-----------+-----+
| ID | ChannelID | ID2 |
+----+-----------+-----+
|  1 |        10 |   1 |
|  2 |        20 |   2 |
|  3 |        30 |   3 |
+----+-----------+-----+
3 rows in set (0.00 sec)mysql>