i try to create an trigger in phpmyadmin.
I will insert some values from a php formular into an mysql table..
but bevor the customer data will insert, i want to check by a trigger, does the customer data already exists. if it exists, i want to value the id, and update the id in another table. If the customer does not exist it should save the customers data.
I tryed this code but it doesent work
(SELECT * FROM `custmr` WHERE `firstname` = NEW.firstname AND `lastname` = NEW.lastname)
UPDATE `repair` SET cust_id = (SELECT id FROM custmr WHERE firstname = NEW.firstname and lastname = NEW.lastname)
INSERT INTO `custmr` (genre, firstname, lastname, street, zip, city, phone, Mobil, mail ) VALUES (NEW.genre, NEW.firstname, NEW.lastname, NEW.street, NEW.zip, NEW.city, NEW.phone, NEW.Mobil, NEW.mail )
create a unique index on first + lastname
ALTER TABLE `custmr` ADD UNIQUE KEY idx_first_last (firstname, lastname);
then you can always use insert your data and if you have a duplicate key they will update the row.
sample usage of ON DUPLICATE KEY
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);