Copy new foreign key number into another table using a trigger

Question

Copy new foreign key number into another table using a trigger

research-1077696

SSC Veteran

Points: 223
More actions
June 16, 2010 at 1:32 pm

#225771

I have two tables where I want to link the tables in effectively a one to one relationship.
Table 1
CustID (PK, nchar(9), Not Null)
Cname(nvarchar(75),Not Null)
Table 2
CTEID(PK, int, Not Null) - note this is set to autoseed
CustID(FK, nchar(9), Not null.
I need to write a trigger which will, upon a new insert into the table, copy the primary key number from Table 1 into Table 2 CustID field which then should allow the auto seed to fire and produce a new primary key number on table 2.
Any ideas?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

This is not tested but assuming by auto seed you are indicating that this is an identity column then I beleive the following trigger would work. Keep in mind this is not tested.

CREATE TRIGGER [dbo].[TCI_CTEID]

ON [Sandbox].[dbo].[table1]

FOR INSERT

AS

SET IDENTITY_INSERT table ON

INSERT INTO Sandbox.dbo.table2 (CTEID)

SELECT CTEID from Inserted

END

SET IDENTITY_INSERT table OFF

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

stamic SSC Eights! Points: 867 More actions · Answer 2

-- use your test database

USE Test

GO

-- create table1

CREATE TABLE table1 (

CustID INT IDENTITY,

Cname NVARCHAR(75) NOT NULL)

-- create table2

CREATE TABLE table2(

CTEID INT IDENTITY,

CustID NVARCHAR(9) NOT NULL)

GO

-- create trigger on table1

CREATE TRIGGER trgCustID ON table1

AFTER INSERT AS

INSERT INTO table2(CustID)

SELECT CustID FROM INSERTED

GO

-- insert record into table1

INSERT INTO table1(Cname)

SELECT 'CustName1'

-- show records from both tables

SELECT * FROM table1

SELECT * FROM table2

-- drop both tables

DROP TABLE table1

DROP TABLE table2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Without a trigger you could:

CREATE TABLE [dbo].[Table1]([Custid] [nchar](9) NOT NULL,[Cname] [nvarchar](75) NOT NULL,

CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([Custid] ASC))

CREATE TABLE [dbo].[Table2]([CTEID] [int] IDENTITY(1,1) NOT NULL,[CustId] [nchar](9) NOT NULL,

CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ([CTEID] ASC))

CREATE PROCEDURE InputIntoTable1Table2

@Custid NCHAR(9),

@Cname NVARCHAR(75)

AS

INSERT INTO Dbo.Table1 (CustId,Cname)

VALUES(@Custid,@Cname)

INSERT INTO Table2(CustId) VALUES(@Custid)

InputIntoTable1Table2 'xxxx','Jones'

Results:

SELECT * FROM Table1

CustidCname

xxxx Jones

SELECT * FROM Table2

CTEIDCustId

1xxxx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 4

research-1077696 (6/16/2010)
I have two tables where I want to link the tables in effectively a one to one relationship.
Table 1
CustID (PK, nchar(9), Not Null)
Cname(nvarchar(75),Not Null)
Table 2
CTEID(PK, int, Not Null) - note this is set to autoseed
CustID(FK, nchar(9), Not null.
I need to write a trigger which will, upon a new insert into the table, copy the primary key number from Table 1 into Table 2 CustID field which then should allow the auto seed to fire and produce a new primary key number on table 2.
Any ideas?

If this is, in fact, a one-to-one relationship, then you can use the CustID as the PK on Table2 and not worry about having an IDENTITY column (CTEID) in that table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

research-1077696 SSC Veteran Points: 223 More actions · Answer 5

Dan,

Great movie and quote. The code did cause a violation of the unique constraint that is on table 2 tuple CustomerID. What we are attempting to do is cause a one to one relationship between the customer table and the customer entity table.

research-1077696 SSC Veteran Points: 223 More actions · Answer 6

Stamic,

The code you suggest has worked even with a unique constraint. What is and where did you find the "FROM INSERTED"?

research-1077696 SSC Veteran Points: 223 More actions · Answer 7

WayneS,

This is in fact a one-to-one relationship. Do you believe a stored procedure would be a better choice then a trigger in this situation?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

research-1077696

Note that the T-SQL code that I posted using my assumption of your table structure, which of course is most likely incorrect in that I selected as the primary key for table2 column CTEID, not a good choice on my part, for if you execute twice the sample given that is:

InputIntoTable1Table2 'xxxx','Jones'

it will result in a error message AND

Table1

CustidCname

xxxx Jones

Table2

CTEIDCustId

1xxxx

2xxxx

Not exactly what you are expecting. So in either case (trigger or SP), consider running the code within a transaction with a try and catch block included so that if an error is detected the transaction can be rolled back. Also consider making the CustId the primary key rather than CTEID as I did in my example.

Consider adding a foreign key constraint to enforce referential integrity (see BOL for further info and how to create and why to use.

)

Further expand your thinking ... eventually an entry in Table1 will have to be deleted/updated, in case of a delete do you want to define a cascading delete to remove the entry from Table2.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dan.Humphries SSChampion Points: 12125 More actions · Answer 9

I apologize I did not see the columns correctly and my code was based on the wrong ones.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.