foreign key issue in one transaction, please help

geo_h · **Joined:** Sun Jun 22, 2008 10:08 pm **Posts:** 4

Hey guys,

I'm having problem getting the scenario below working in one transaction.

Assume:
Table #1 - "salesTable"
Table #2 - "salesHistory"

salesTable has the following composite ID (customerId, salesId)
note# customerId is known before hand, salesId is generated (+1) during each insert.

salesHistory has the same composite ID (customerId, salesId)
I need the salesId in the salesHistory to be the same as the salesTable's salesId.

How can I do that in one transaction, so that the salesHistory's saleId can get the generated id from the salesTable?

thx so much,
George

zafarjcp · **Joined:** Sun May 18, 2008 1:21 pm **Posts:** 13

George

There are two ways to do this.

1. First get the newly generated SalesId its basically by OutParamenter or Identity. and now you have both CustomerId and Salesid for your Sales HIstory table.

2. Database Triggers.

Create a Trigger which fires on Insert of ever new record of first table and in that tirgger you can get CustomerId , newly generated salesId and can easily post it to SalesHistory Table.