Can you look up a subsequent FK constraint from query in Mysql using triggers?

Can you look up a subsequent record like the below using a MySQL trigger and omitting the ID lookup SELECT statement in the original query?


INSERT INTO _skills_emp_link (skill_id, emp_id, region_id)
                         VALUES (‘2.’, ‘4’, (SELECT region_id FROM employees WHERE employee_id = ‘4’));

Open in new window


Basically I would like to issue a Query like:
 INSERT INTO _skills_emp_link (skill_id, emp_id) VALUES ('2', '4') 

Open in new window


and a BEFORE or AFTER INSERT trigger to take the emp_id 4 (entered in the initial query) and look up the attributed region_id and append the SQL query so that the FK constraints work properly.
Florin PetrutiuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
When the region depends on the employee, why storing the region redundantly? This sounds like a model issue.
Steve BinkCommented:
ste5an is right on.  You already have the region ID being stored with the employee.  You should not be storing it again in the dependent table.  Any queries for information on _skills_emp_link that do need the region ID should be joined back to the employee table to get it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.