php - 具有嵌套选择语句的 MySQL 触发器

Question

我在使用 magento 数据库中的触发器语法时遇到一些困难。

我想在创建新地址时更新客户表中的值。

customer_address_entity_varchar 表如下所示:

value_id | entity_type_id | attribute_id | entity_id | value
48       | 2              | 28           | 3         | Lancashire

当我在没有嵌套选择语句的情况下执行触发器时，它可以工作，所以:

IF (new.attribute_id=28) THEN
UPDATE customer_entity SET customer_entity.group_id = 4
WHERE customer_entity.entity_id = 1
END IF;

我尝试使用 new.entity_id 和 new.value 从其他表中选择值来替换 4 和 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;

在 MySQL 中创建触发器成功，但是当我尝试在 Magento 中添加新地址时，出现错误，但没有告诉我任何信息。

感谢您的帮助。利亚姆

Answer 1

我通过分解我想要做的事情来解决这个问题。

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