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