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

Florin Petrutiu
Florin Petrutiu used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
When the region depends on the employee, why storing the region redundantly? This sounds like a model issue.
Top Expert 2004

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial