Best Practices / Good Strategy?

System

I have a likely common scenario and I'm interested in hearing some thoughts on how to go about this:

I have two tables, Table A and Table B. Table B has a dependency on Table A and when a certain type of DCR is pushed through workflow, each table is updated.

Table A has a primary key which is maintained (i.e. autoincremented) by SQL Server and therefore, TeamSite has no knowledge of this value. However, if I want to cascade an update to both tables, I will need to know this value, as Table B requires it. So, I'm hoping there's a cleaner / more succinct way to do this than to (1) insert into Table A, (2) retrieve last_insert_id() (or equivalent) from SQL Server, (3) insert into Table B, (4) if failure, delete inserted value from Table A.

Thanks,

Dave

Migrateduser

Do the columns that you are using as a composite primary key from OD's perspective in table A exist in table B? If so, you could let OD manage the relationship. Incidentally, that's a major point of debate among OD enthusiasts - whether its better to let OD manage relational integrity or handle it via triggers in the DB. I lean toward the latter, but I think my colleague Josh leans a little more toward the former. As always, the right answer is "it depends".

In a worst case, you could define an additional column strictly for the purposes of being the key - perhaps PATH.

But we digress...

If you can't use the key that you have defined, then you are either stuck with the hack you suggested, which is subject to a race condition, or you could potentially write a tuple pre-processor for the second table that would pull the value. Since it would be bundled inside a transaction - it might mitigate the potential of the race condition. That said, I make no warranties, express or implied