在INSERT ON DUPLICATE KEY上触发 [英] Trigger on 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 INSERT
and 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.
这篇关于在INSERT ON DUPLICATE KEY上触发的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!