Re: INSERT / UPDATE into 2 inner joined table simultaneously

Normally I would not recommend trying to update / insert / delete 2 joined tables simultaneously, but....  if you have a real business or technical reason to do this, alos look at 'INSTEAD OF' triggers on views, where the view would contain your joined SQL statement. The instead of trigger can then contain whatever SQL your heart desires for update / insert / delete functionality..

Just another option to the CTE mentioned before, and allows some complex logic to be implemented.

In a past life we had a database where we stored most real life data objects (people, organizations, systems, programs and so on) in generic object tables where the objects were logically related to each other in several different ways.  Due to constant and unforeseeable changes in relationships between data objects and object types and structures, we created a generic structure that stored each object in  the same table / tables with a base set of columns and then a set of generic columns and an overflow table for additional data elements (JSON, where were you?...).  This method also hid the complexity of date-tracking changes to each object and relationship from the developer.

We ended up with a database with tens of tables, instead of hundreds.

To cut a long story short, we used views to flatten this ll out and to represent the individual object types.  We gave these views CRUD functionality  using  'INSTEAD OF' triggers,that were coded up and tested by the DB dev team, and then distributed to the development team, who thought they were working with simple table structures.

As a side benefit, this was a large, multi-million dollar project in a well known US financial institution with very rigorous devops procedures around database structure changes, that could take a week or two to request and change ANY table or column structure in the dev /  test DBs, and even longer to move into prod.  Meeting project delivery timelines would have been dead in the water if we had used a traditional fully normalized structure due to constant changes as we brought new systems and departments into the application (identity and access management).  For some reason, stored procedures and views could slip by under the DB approval process radar screen as they were regarded as app dev objects.

Design was well proven in their production environment, and we took that same design forward into several other large clients and implemented the entire code base with no DB design or structure changes, just configuration of the master data tables driving it.