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.

On Wed, 6 Mar 2019 at 21:59, Lou <lou(at)dayspringpublisher(dot)com> wrote:

> Hi everyone,
>
> This is my first post here.
>
> I have two tables named c and p. When using SELECT, they are linked using
> an INNER JOIN like in this example: "SELECT * FROM c INNER JOIN p ON c.id
> = p.c_id WHERE name = 'Jones';"
>
> How can I INSERT new rows into both tables simultaneously with
> automatically created id numbers, and how can I UPDATE both tables
> simultaneously?
>
> Lou
>