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