Replicate primary key for the foreign key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replicate primary key for the foreign key

Author

Topic

PaulNeil
Starting Member

9 Posts

Posted - 2007-07-14 : 00:35:08

Hey Guys,

I am just new to SQL Server. I have just created 2 tables Table1 and Table2. Table1 has a primary key (Table1.ID), which has been set on identity values.Both tables have different columns except the ID columns.

I have also used Table1.ID as foreign key and primary key on Table2 as Table2.ID.

I am just wondering guys if it is possible that when I enter or insert a data on Table1, can the same value (Table1.ID) be instantaneously copied to Table2.ID for the next data entry for Table2.

Please could you show me how this can be done using SQL Server Express 2005.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-14 : 00:59:14

You can create insert trigger on table1.

PaulNeil
Starting Member

9 Posts

Posted - 2007-07-14 : 01:07:04

Thanks rmiao for your response. I guessed as much that trigger is needed, but as I expressed earlier I'm just new to this. I will be grateful if you could post the code for the trigger needed for this event. Thank you once again.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-14 : 01:16:53

Run something like 'insert into table2 ... select ... from inserted' in the trigger, check books online for details.

PaulNeil
Starting Member

9 Posts

Posted - 2007-07-14 : 16:41:37

I have tried creating a trigger below for this event:

CREATE TRIGGER [dbo].[ID_Insert_Update]
ON [dbo].[TABLE1]
AFTER INSERT, UPDATE
AS
DECLARE @Variable int
SET @Variable = Scope_Identity()

INSERT INTO TABLE2 (PROVIDER_ID) VALUES (@Variable)

For some reason the insert on both tables failed.

As I have said earlier I am new to these, I hope you do bear with me. Thank you very much for your help.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-14 : 20:01:23

Is table2.id an identity column? You should get value from inserted table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources