Need help with MySQL trigger syntax involving subqueries
I’m working on a trigger for my e-commerce database and running into issues with the syntax when using nested SELECT statements.
I want to automatically update a field in the users table whenever a new location record gets inserted.
My address_details table structure:
record_id | type_id | attr_id | user_id | data
15 | 1 | 25 | 7 | California
This basic version works fine:
IF (new.attr_id=25) THEN
UPDATE users SET users.category_id = 2
WHERE users.user_id = 5
END IF;
But when I try to make it dynamic using new.user_id and new.data with subqueries, it fails:
DROP TRIGGER IF EXISTS `category_updater`;
CREATE DEFINER=`db_user`@`localhost` TRIGGER `category_updater`
AFTER INSERT ON `address_details`
FOR EACH ROW
IF (new.attr_id = 25) THEN
UPDATE users
SET users.category_id = (
SELECT region_mapping.group_id
FROM region_mapping
WHERE region_mapping.region_name = CONCAT(new.data))
WHERE users.user_id = (
SELECT address_master.owner_id
FROM address_master
WHERE address_master.address_id = new.user_id);
END IF;
The trigger creates successfully but throws an error when actual data gets inserted through the application.