带有嵌套select语句的MySQL Trigger

问题描述：

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

我的magento数据库中的触发器语法有些困难。 p>

我想在创建新地址时更新customer表中的值。 p>

customer_address_entity_varchar表如下所示： p>

  value_id |  entity_type_id |  attribute_id |  entity_id | 价值
48 |  2 |  28 |  3 |  Lancashire 
  code>  pre> 
 
 当我在没有嵌套的select语句的情况下执行触发时，它可以工作，所以： p> 
 
 
  IF（new  .attribute_id = 28）THEN 
UPDATE customer_entity SET customer_entity.group_id = 4 
WHERE customer_entity.entity_id = 1 
END IF; 
  code>  pre> 
 
 我正在尝试使用new。  entity_id和new.value从其他表中选择值来替换4和1： p> 
 
 
  DROP TRIGGER IF EXISTS`groupid_update`; 
创建DEFINER =`rock_store` @  localhost`TRIGGER`groupid_update` 
 
 
 
 
来自`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 \  n 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; 
  code>  pre> 
 
 在MySQL中创建触发器是成功的，但是当我尝试在Magento中添加新地址时，我收到的错误告诉我什么。   p> 
 
 
感谢您的帮助。 利亚姆 p> 
  div>

答

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

带有嵌套select语句的MySQL Trigger

相关推荐