Re: DELETE trigger, direct or indirect?

On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> You have two tables with list in their name, so are rows deleted from
> both. Just to be clear enity_list should actually be entity_list?
>
> Also how are entity and enity_list related?
>

I have to confess that your questions surprise me a bit.
I thought the model was pretty clear from the SQL.
We have two entities, Foo (my entity table), and another Bar (my
entity_list table),
with Bar reference 0 or more Foos, recording which ones it references in an
association table.

Are the values for the name field in entity and enity(entity)_list the
> same for a given entity?
>

The name of Foo and Bar are completely independent.

> > transfering the old entity name to it thanks to an UPDATE on
> > entity_name, thus preserving the old name.
>
> How?
> Again how would you determine where the action started?
>

Deleting a Foo cascades to the _member assoc-table.
If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member.
I no longer care where the action started.

> > Then an INSERT trigger on entity could locate any (indexed) "stashed"
> > entity names in that extra non-FK column in entity_list_member,
>
> How would it locate it if the name that defined the FK(entity(name)) was
> NULL?
>

In the extra non-FK column I mentioned explicitly, in the _member
assoc-table.