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(dot)klaver(at)aklaver(dot)com
> <mailto: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.

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(dot)klaver(at)aklaver(dot)com