在INSERT ON DUPLICATE KEY上触发

本文介绍了在INSERT ON DUPLICATE KEY上触发的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个简单的问题.我有一个触发器可以将值插入另一个数据库

I have a simple question. Ive got a trigger to insert the values into another Database

例如，如果有两个值，并且触发器正在检查Table A中的值并将其插入Table B

So for example if there are two values and the trigger is checking the value in Table A and inserting into Table B

这是代码

-- Trigger DDL Statements USE `db`; DELIMITER // CREATE DEFINER=CURRENT_USER() TRIGGER `db`.`AFTER_INSERT_A` AFTER INSERT ON `db`.`a` FOR EACH ROW BEGIN IF NEW.val!= NULL THEN UPDATE b SET dateRemove=CURRENT_TIMESTAMP WHERE val=NEW.val; INSERT INTO b (val) VALUES(NEW.val) ON DUPLICATE KEY UPDATE dateRemove=NULL, dateUpdate=CURRENT_TIMESTAMP; END IF; END//

触发点甚至会引发任何错误.而且我在B中没有值

The trigger dosent even throw any errors. And i have no values in B

我的插入内容是

INSERT INTO a (val) VALUES(`test`) ON DUPLICATE KEY UPDATE dateUpdate=CURRENT_TIMESTAMP

任何人都有任何想法.我尝试创建两个触发器，一个为INSERT，另一个为UPDATE，我将AFTER更改为BEFORE，而我的表b仍然没有任何响应.任何想法在此先感谢

Has any one got any ideas. Ive tried creating two triggers one INSERTand the other UPDATE, Ive changed the AFTER to BEFORE and my table b still got nothing. Any ideas thanks in advance

推荐答案

要在诸如

INSERT INTO table (x, y, z) VALUES('x', 'y', 'z') ON DUPLICATE KEY UPDATE x=VALUES(x);

必须始终在BEFORE INSERT触发器上运行.

must always be run on a BEFORE INSERT Trigger.