DELETE trigger, direct or indirect?

On 16/02/2023 14:23 CET Dominique Devienne <ddevienne@gmail.com> 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).

To achieve this weird requirement, I'd like to know if it is possible in an
ON DELETE trigger to know whether the deletion is coming from a direct-DELETE
in the "child table", or whether the deletion is coming from the "parent
table" CASCADEd to the child table.

Not to my knowledge. ON DELETE CASCADE behaves like a manual DELETE on the
child table that happens before the DELETE on the parent table.

The process you describe shows that it's not known until the end of the
transaction which parent rows can be deleted. You can instead track the
parent rows as candidates for deletion in a temp table. Insert the primary
key of parent rows if you deem them deletable and delete the primary key if
you detect the opposite. At the end the temp table only contains IDs of
parent rows that can be deleted for sure.

--
Erik

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?

To achieve this weird requirement, I'd like to know if it is possible in
an ON DELETE trigger to know whether the deletion is coming from a
direct-DELETE in the "child table", or whether the deletion is coming
from the "parent table" CASCADEd to the child table.

Thanks, --DD

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian.klaver@aklaver.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?

David J.

On 2/16/23 08:55, David G. Johnston wrote:

On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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.klaver@aklaver.com

On Thu, Feb 16, 2023 at 5:59 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/16/23 08:55, David G. Johnston wrote:

On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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.

OK, I started writing SET NULL won't help, but I'll back up and try to give
more info, as requested.
Pseudo SQL at this point.

create table entity (name text primary key, ...);
create table enity_list (name text primary key, ...);
create table entity_list_member(
list_name text not null references entity_list(name) on delete cascade on
update cascade,
entity_name text not null references entity(name) on delete cascade on
update cascade
primary key (list_name, entity_name)
);

Above is the current situation. When the entity is deleted, it's implicitly
deleted from all list that mention it.
Referential Integrity 101 I guess. But apparently, it's common enough for
an entity to be deleted and reloaded,
not necessarily in the same transaction, that losing the list(s) membership
on delete is considered "a bug".

One solution is to not do any reference integrity in the lists. But that
opens the door to garbage in a little too wide I think.

So on second thought, maybe the SET NULL could be of use. I'd add a second
non-FK column on the member assoc-table,
transfering the old entity name to it thanks to an UPDATE on entity_name,
thus preserving the old name.
Then an INSERT trigger on entity could locate any (indexed) "stashed"
entity names in that extra non-FK column in entity_list_member,
to retransfer the name back to the primary FK column.
I'd need to adjust the PK to a coalesce(), and ensure the two columns are
mutually exclusive.
Sounds like that might work, no?

On 2/16/23 09:17, Dominique Devienne wrote:

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

OK, I started writing SET NULL won't help, but I'll back up and try to
give more info, as requested.
Pseudo SQL at this point.

create table entity (name text primary key, ...);
create table enity_list (name text primary key, ...);
create table entity_list_member(
  list_name text not null references entity_list(name) on delete
cascade on update cascade,
  entity_name text not null references entity(name) on delete cascade
on update cascade
  primary key (list_name, entity_name)
);

Above is the current situation. When the entity is deleted, it's
implicitly deleted from all list that mention it.
Referential Integrity 101 I guess. But apparently, it's common enough
for an entity to be deleted and reloaded,
not necessarily in the same transaction, that losing the list(s)
membership on delete is considered "a bug".

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?

One solution is to not do any reference integrity in the lists. But that
opens the door to garbage in a little too wide I think.

So on second thought, maybe the SET NULL could be of use. I'd add a
second non-FK column on the member assoc-table,

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

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?

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?

to retransfer the name back to the primary FK column.
I'd need to adjust the PK to a coalesce(), and ensure the two columns
are mutually exclusive.
Sounds like that might work, no?

--
Adrian Klaver
adrian.klaver@aklaver.com

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.

On 2/16/2023 12:28 PM, Dominique Devienne wrote:

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.

Another option would be to not delete the records, but add a Deleted
column and mark them as deleted.
Your CASCADE then becomes an UPDATE trigger.
Restoring the relationship would then be a simple matter of unmarking
them as deleted.

I haven't tried this, but it's possible that you could hijack the DELETE
trigger so the app didn't have to change how it deletes records.
If you were really insistant on the app not changing to respect the
deleted flag, you could add views and read from those.

If you potentially have a lot of deleted records, and you have a time
frame after which it would be unlikely they would be restored, then you
could add a DeletedDate field. After a given amount of time do garbage
cleanup on anything over that threshold.

We don't have that many deletes, so we just leave them. They don't show
up in the app, since they are "deleted" but we have an admin mode that
can ignore the deleted flag if the user chooses and they can then
undelete any records.
So nothing ever gets literally deleted, but they do get archived after
18 months. And again, we have a mode where you can include archived records.

In your situation, when they undelete the parent record, it could
automatically undelete the children.

Brad.

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