I am having some difficulty with the syntax for a trigger in my magento database.
I want to update a value in the customer table when a new address is created.
The customer_address_entity_varchar table looks like this:
value_id | entity_type_id | attribute_id | entity_id | value
48 | 2 | 28 | 3 | Lancashire
When I do the trigger without the nested select statements it works, so:
IF (new.attribute_id=28) THEN
UPDATE customer_entity SET customer_entity.group_id = 4
WHERE customer_entity.entity_id = 1
END IF;
I am trying to use new.entity_id and new.value to select values out of other tables to replace the 4 and 1:
DROP TRIGGER IF EXISTS `groupid_update`;
CREATE DEFINER=`rock_store`@`localhost` TRIGGER `groupid_update`
AFTER INSERT ON `customer_address_entity_varchar`
FOR EACH ROW
IF (new.attribute_id = 28) THEN
UPDATE customer_entity
SET customer_entity.group_id = (
SELECT directory_country_region_name.customer_group_id
FROM directory_country_region_name
WHERE directory_country_region_name.name = "'" + new.value + "'")
WHERE customer_entity.entity_id = (
SELECT customer_address_entity.parent_id
FROM customer_address_entity
WHERE customer_address_entity.entity_id = new.entity_id);
END IF;
Creating the trigger in MySQL is successful, but when I try to add a new address in Magento I get an error which tells me nothing.
Thanks for your help. Liam
I managed to solve this by breaking down what I was trying to do.
IF (new.attribute_id = 28) THEN
SET @add_entity_id = new.entity_id;
SET @county = new.value;
SET @group_id = (SELECT directory_country_region_name.customer_group_id
FROM directory_country_region_name
WHERE directory_country_region_name.name = @county);
SET @customer_id = (
SELECT customer_address_entity.parent_id
FROM customer_address_entity
WHERE customer_address_entity.entity_id = new.entity_id);
UPDATE customer_entity SET customer_entity.group_id = @group_id WHERE customer_entity.entity_id = @customer_id;
END IF