mercredi 13 juillet 2016

MySQL insert query if condition matches in after insert trigger

I'm using WampServer with MySQL Version: 5.7.9 and phpMyAdmmin Version: 4.5.2

I'm trying to write an after insert trigger on Table1 to insert data in Table2 if the data doesn't exist in Table2. Below is my query:

DROP TRIGGER IF EXISTS `insert_customer`;
CREATE TRIGGER `insert_customer` AFTER INSERT ON `installations`
FOR EACH ROW BEGIN
    IF (SELECT COUNT(*) FROM `customers` WHERE `customers`.`phone` = NEW.`phone`) = 0
    THEN INSERT INTO `customers` (`phone`, `imei`, `platform`, `version_code`) VALUES (NEW.`phone`, NEW.`imei`, NEW.`platform`, NEW.`version_code`);
    END IF;
END;

I've tried with delimiter too:

DROP TRIGGER IF EXISTS `insert_customer`;
DELIMITER |
CREATE TRIGGER `insert_customer` AFTER INSERT ON `installations`
FOR EACH ROW BEGIN
    IF (SELECT COUNT(*) FROM `customers` WHERE `customers`.`phone` = NEW.`phone`) = 0
    THEN INSERT INTO `customers` (`phone`, `imei`, `platform`, `version_code`) VALUES (NEW.`phone`, NEW.`imei`, NEW.`platform`, NEW.`version_code`)
    END IF;
END;
|
DELIMITER ;

Both queries are giving me error. Any help?

Aucun commentaire:

Enregistrer un commentaire