Postgres Pro
Downloads
Home   >   mailing lists

Re: DELETE trigger, direct or indirect? - Mailing list pgsql-general

From Dominique Devienne
Subject Re: DELETE trigger, direct or indirect?
Date
Msg-id CAFCRh--E-JC244cGOhVxmroQJaGNwbCp+a9a5soRpoe5-4aLYQ@mail.gmail.com
Whole thread Raw
In response to Re: DELETE trigger, direct or indirect?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: DELETE trigger, direct or indirect?  (Brad White <b55white@gmail.com>)
Re: DELETE trigger, direct or indirect?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Tree view
On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <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.
 
> 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.

pgsql-general by date:

Previous
From: Dominique Devienne
Date:
Subject: Re: How to avoid Trigger ping/pong / infinite loop
Next
From: Brad White
Date:
Subject: Re: DELETE trigger, direct or indirect?