MySQL-插入后更新同一表的触发器
我想做的是:
当有一个新的INSERT放到桌子上ACCOUNTS,我需要更新行ACCOUNTS哪里pk = NEW.edit_on通过设置status='E'表示特定(旧)帐户已被编辑。DELIMITER $$DROP TRIGGER IF EXISTS `setEditStatus`$$CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTSFOR EACH ROW BEGIN
update ACCOUNTS set status='E' where ACCOUNTS.pk = NEW.edit_on ;END$$DELIMITER ;
要求是不我操纵新插入列,但已经存在列pk = NEW.edit_on
但是,我不能更新同一个表:Can't update table ACCOUNTS ... already used by the statement that invoked this trigger
请提出一个解决办法
编辑
ACCOUNTS表:CREATE TABLE `ACCOUNTS` (
`pk` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(9) unsigned NOT NULL,
`edit_on` bigint(10) unsigned DEFAULT NULL,
`status` varchar(1) NOT NULL DEFAULT 'A',
PRIMARY KEY (`pk`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2147483726 DEFAULT CHARSET=latin1