Data base tables connection

[pawan768]

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)

[BitBlt]

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.

[pawan768]

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.

[urtrivedi]

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

[adam_k]

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.