Posted by Erland Sommarskog on 07/02/05 19:09

(rjl444@hotmail.com) writes:
> I created 2 tables with one to one relationship. if I add a record in
> table A, how does table B record get created?

You insert it, one way or another.

> Does SQL do this automatically because it is one to one relationship?

No. SQL Server can't really see what you are doing. It can tell if the
relation is one-to-one, or one-to-optional-one.

> or do I need to create a trigger? if i need a trigger, how do I get the
> ID of new record to create the same ID in table B?

Trigger is one way. That presumes that you have information enough in
the trigger to insert that other row. Copying the ids of the inserted
rows in A to table B is a trivial matter:

CREATE TRIGGER A_tri ON A FOR INSERT AS
INSERT B (keycol)
SELECT keycol FROM inserted

Note that a trigger fires once per statement, and must be able to
handle multi-row operatons. But this only possible if all rows in B
beside the key colunm are nullable.

(If you were silly enough to attribute the key column in B as IDENTITY,
you need to change that.)

You can also issue to separate INSERT statments, one for each table. That
would be necessary if you have data for both tables ar this point.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]