这就是我想做的:
当有新的INSERT
到表中ACCOUNTS
,我需要更新行ACCOUNTS
where pk
= NEW.edit_on
通过设置status='E'
表示特定(旧)帐户已被编辑。
DELIMITER $$
DROP TRIGGER IF EXISTS `setEditStatus`$$
CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTS
FOR EACH ROW BEGIN
update ACCOUNTS set status='E' where ACCOUNTS.pk = NEW.edit_on ;
END$$
DELIMITER ;
要求是NOT我操纵新插入的列,而是一个已经存在的列与pk = NEW.edit_on
但是,我无法更新同一张表:Can't update table ACCOUNTS ... already used by the statement that invoked this trigger
请提出解决方法
PS:我已经经历过了在同一个表上更新后更新触发器中的表 https://stackoverflow.com/questions/4547465/updating-table-in-trigger-after-update-on-the-same-table, 插入到同一个表触发器mysql https://stackoverflow.com/questions/8504904/insert-into-same-table-trigger-mysql, 在同一个表上使用插入后触发器进行更新 https://stackoverflow.com/questions/6925651/update-with-after-insert-trigger-on-same-table and mysql触发器在表上插入后插入和更新 https://stackoverflow.com/questions/12870465/mysql-trigger-with-insert-and-update-after-insert-on-table但他们似乎没有回答我的问题。
Edit
ACCOUNTS
Table:
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