带有嵌套select语句的MySQL Trigger

Question

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

Answer 1

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