Re: DELETE trigger, direct or indirect?

On 2/16/23 10:28, Dominique Devienne wrote:
> On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.

What happens if an entity_list value is deleted?

Are you going to replicate the above for it to?

If so something like what Brad White suggested would seem  to  simpler.

Or, create a history table where rows deleted from entity_list_member 
are moved to.

> 
>      > 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.

-- 
Adrian Klaver
adrian.klaver@aklaver.com