How to connect table's columns of same database

I have a database say database_Employee with 2 tables, record and salary. Now both tables have 开发者_Python百科a column EmpId.

What I want is when I update (or add to) EmpId value in the record table, then EmpId column of salary should get the value automatically (what I added in EmpId of tb1).

I am using Visual Studio 2010, .NET Framework 4.0, SQL Server Express.

If my question is not clear, let me know.

---

For something like this, where you want to keep two tables values in sync, you should think about triggers. They would go into the SQL server layer. Since there don't seem to be any business rules associated with this synchronization, triggers will make sure that the data will be copied from one table to the next, regardless of the program.

---

I am not really sure what you want to achieve but:

on option would be to setup a insert/update/delete trigger in the db on the table "record" which in turn changes values in table "salary".

another option would be use a stored procudure for the update/insert... in the stored procedure can access both tables as needed...

---

You shouldn't do that. If EmpId can change, you should create a new Id column in record, just for the database. You then have a foreign key column in salary that references this record.Id. This way salary doesn't need EmpId. If you want to get the EmpId for a certain salary column, you join on the Id column. If you want to change EmpId, just change it on record, that's enough.

This assumes you have a good reason to keep the table separate. Another option would be to combine them into one table.

EDIT:

Based on a comment to this answer (which makes it a completely different question, I think):

If, as you say, manager creates the Details row and after that, HR adds his Accounts data, then there is no reason to create the Accounts row when you create the Details row. Create the row when the HR sets the account data for the first time.

Another solution (already proposed by others) is to merge the two tables. If you always have 1: relationship between them, there is no need to keep the data in different tables. You could create views if you wanted to look only at a part of the data.

Yet another solution, which comes closest to what you are asking (but may not be the best option) is to use triggers. When a row is inserted into the Details table, a trigger fires and a row is created in the Accounts table.