Re: DELETE trigger, direct or indirect?

On 2/16/23 08:55, David G. Johnston wrote:
> On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 2/16/23 05:23, Dominique Devienne wrote:
> > Hi. This is a bit unusual. We have a foreign key between two tables,
> > with ON DELETE CASCADE, to preserve referential integrity. But we
> > apparently also need to preserve the severed reference (by
> natural key,
> > i.e. its name), to later on reconnect the two entities
> after-the-fact,
> > should the parent row re-appear later on (in the same transaction
> or not
> > it still unclear).
>
> This is going to need a more detailed description of the relationship
> between the two tables:
>
> 1) The actual FK relationship.
>
> 2) What "...preserve the severed reference (by natural key,  i.e. its
> name)" means?
>
> 3) What information will be used to reconnect the child rows to the
> parent rows?
>
>
> Maybe the OP should be using ON DELETE SET NULL instead of CASCADE?

That is where I am headed, however it will need more information to
determine whether that makes sense or not.

>
> David J.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com