table relationships, SQL 2005

Question

Ok I have a question and it is probably very easy but I can not find the solution.

I have 3 tables plus one main tbl.

tbl_1 - tbl_1Name_id 
tbl_2- tbl_2Name_id 
tbl_3 - tbl_3Name_id

I want to connect the Name_id fields to the main tbl fields below.

main_tbl 
___________ 
tbl_1Name_id 
tbl_2Name_id 
tbl_3Name_id

Main tbl has a Unique Key for these fields and in the other table, fields they are normal fields NOT NULL.

What I would like to do is that any time when the record is entered in tbl_1, tbl_2 or tbl_3, the value from the main table shows in that field, or other way. Also I have the relationship Many to one, one being the main tbl of course.

I have a feeling this should be very simple but can not get it to work.

Answer 1

Take a look at SQL Server triggers. This will allow you to perform an action when a record is inserted into any one of those tables.

If you provide some more information like:

• An example of an insert
• The resulting change you would like to see as a result of that insert

I can try and give you some more details.

UPDATE

Based on your new comments I suspect that you are working with a denormalized database schema. Below is how I would suggest you structure your tables in the Employee-Medical visit scenario you discussed:

Employee
--------
EmployeeId
fName
lName

EmployeeMedicalVisit
--------------------
VisitId
EmployeeId
Date
Cost

Some important things:

1. Note that I am not entering the employees name into the EmployeeMedicalVisit table, just the EmployeeId. This helps to maintain data integrity and complies with First Normal Form
2. You should read up on 1st, 2nd and 3rd normal forms. Database normalization is a very imporant subject and it will make your life easier if you can grasp them.

With the above structure, when an employee visited a medical office you would insert a record into EmployeeMedicalVisit. To select all medical visits for an employee you would use the query below:

SELECT e.fName, e.lName
FROM Employee e
INNER JOIN EmployeeMedicalVisit as emv
ON e.EployeeId = emv.EmployeeId

Hope this helps!

Answer 2

Here is a sample trigger that may show you waht you need to have:

Create trigger mytabletrigger ON mytable
For INSERT 
AS

INSERT MYOTHERTABLE (MytableId, insertdate)
select mytableid, getdate() from inserted

In a trigger you have two psuedotables available, inserted and deleted. The inserted table constains the data that is being inserted into the table you have the trigger on including any autogenerated id. That is how you get the data to the other table assuming you don't need other data at the same time. YOu can get other data from system stored procuders or joins to other tables but not from a form in the application.

If you do need other data that isn't available in a trigger (such as other values from a form, then you need to write a sttored procedure to insert to one table and return the id value through an output clause or using scope_identity() and then use that data to build the insert for the next table.