Trigger on INSERT ON DUPLICATE KEY

问题:

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

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

So here is the code

-- 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//

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

My Insert is

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

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

回答1:

The trigger to be run on a query such as

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

must always be run on a BEFORE INSERT Trigger.

回答2:

Perhaps instead of

NEW.val!= NULL

you need

NEW.val IS NOT NULL

A value is never equal to null, and it is never not equal to null.