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

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.