0

I try many tips but not able to connect two table with each other!

Actually i want something like that , I insert into one table and if data also used by another table (Data automatically insert in another table also by submitting data in first table)

4
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by adam_k
0

You could use an "insert" trigger for that. Or, insert into both tables in one stored procedure, with appropriate existence testing to check if the data is used by the other table.

0

You could use an "insert" trigger for that. Or, insert into both tables in one stored procedure, with appropriate existence testing to check if the data is used by the other table.

Sir actually i have 2 table ,

employee(emp_id,dept_id(foreign key), name,mobile_No, address) , 
department(dept_id,no_of_employee,location)

when i enter any data on employee in employee table in particular department then , no_of_employee automatically update department-wise as i enter the data in employee table.

0

you need to create after insert trigger on employee table, in which you should write update statement for department table

update department set no_of_employees =no_of _employees+1 where dept_id=new.dept_id

then you create delete trigger
update department set no_of_employees =no_of _employees-1 where dept_id=old.dept_id

then you create update trigger
update department set no_of_employees =no_of _employees+1 where dept_id=new.dept_id
update department set no_of_employees =no_of _employees-1 where dept_id=old.dept_id


http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Edited by urtrivedi: n/a

0

you need to create after insert trigger on employee table, in which you should write update statement for department table

update department set no_of_employees =no_of _employees+1 where dept_id=new.dept_id

then you create delete trigger
update department set no_of_employees =no_of _employees-1 where dept_id=old.dept_id

then you create update trigger
update department set no_of_employees =no_of _employees+1 where dept_id=new.dept_id
update department set no_of_employees =no_of _employees-1 where dept_id=old.dept_id


http://dev.mysql.com/doc/refman/5.0/en/triggers.html

I totally agree, but wouldn't it be easier/safer and done in 1 go to update the number of employees with the count of employees? It can be done with 1 trigger and the number of employees will be correct no matter what.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.