Clearing Parent Item When Deleting Child

[Scott Morgan]

Working in a PSQL procedure, I'm deleting a child entry and I want to
clear the parent entry if the child happens to be it's last reference.

I can do it in a big mess of several statements and comparisons (get
parent key from child entry, delete child, count parent reference in
child table and delete parent if 0)

Is there a neater way to do this? In particular, I only want to affect
the relevant parent entry, so a blanket delete all with count 0 children
isn't right for this case.

Scott

[Svein Erling Tysvær]

I suppose you could try an AFTER UPDATE/DELETE trigger on the table with the children that does a DELETE FROM PARENT... WHERE NOT EXISTS... Of course, this would not work as intended if another user concurrently inserted a child not yet visible to your current transaction.

HTH,

Set

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/e68b5716-16b0-528e-cb5d-f37287e33846%40blueyonder.co.uk.

[Mark Rotteveel]

This sounds like a job for an AFTER DELETE trigger on the child table.
For example, delete if there are no more children

CREATE TRIGGER ai_child_delparent AFTER DELETE ON child
AS
BEGIN
DELETE FROM parent
WHERE id = :old.parent_id
AND NOT EXISTS(SELECT * FROM child WHERE parent_id = parent.id);
END

Or execute unconditionally and ignore the foreign key violation:

CREATE TRIGGER ai_child_delparent AFTER DELETE ON child
AS
BEGIN
DELETE FROM parent
WHERE id = :old.parent_id;
WHEN GDSCODE foreign_key DO
BEGIN
-- ignore
END
END

Mark
--
Mark Rotteveel

[Scott Morgan]

On 17/02/2021 16:20, Mark Rotteveel wrote:
> This sounds like a job for an AFTER DELETE trigger on the child table.
> For example, delete if there are no more children

On 17/02/2021 16:19, Svein Erling Tysvær wrote:
> I suppose you could try an AFTER UPDATE/DELETE trigger

That would be the way, but I only want to do this in this one particular
action, elsewhere in the system it would be fine to delete the child and
leave the parent.

[Mark Rotteveel]

If it only has to happen for that specific action, then you need to add
that delete code to the specific procedure that executes that action, of
course instead of :old.parent_id, you need to use an appropriate local
variable. For example:

CREATE PROCEDURE delete_child (child_id integer)
AS
DECLARE parent_id INTEGER;
BEGIN
DELETE FROM child
WHERE id = :child_id
RETURNING parent_id INTO parent_id;
BEGIN
DELETE FROM parent
WHERE id = :parent_id;

WHEN GDSCODE foreign_key DO
BEGIN
-- ignore
END
END

END

NOTE: The nested BEGIN .. END block is necessary to prevent a foreign
key violation when deleting from child to be ignored as well.

Or use RDB$SET_CONTEXT / RDB$GET_CONTEXT to signal to the trigger if it
is appropriate to run or not.

Mark
--
Mark Rotteveel

[Scott Morgan]

On 17/02/2021 16:50, Mark Rotteveel wrote:
>
> CREATE PROCEDURE delete_child (child_id integer)
> AS
>   DECLARE parent_id INTEGER;
> BEGIN
>   DELETE FROM child
>     WHERE id = :child_id
>     RETURNING parent_id INTO parent_id;
>   BEGIN
>     DELETE FROM parent
>       WHERE id = :parent_id;
>     WHEN GDSCODE foreign_key DO
>     BEGIN
>       -- ignore
>     END
>   END
> END
>
> NOTE: The nested BEGIN .. END block is necessary to prevent a foreign
> key violation when deleting from child to be ignored as well.
>

That looks good to me, don't think I realised about the nestling being
necessary. Thanks.

Scott

[ma...@lawinegevaar.nl]

Op woensdag 17 februari 2021 om 18:13:18 UTC+1 schreef Scott Morgan:

That looks good to me, don't think I realised about the nestling being
necessary. Thanks.

To be clear, that nesting is only necessary because of the error handling. WHEN ... DO ... handles the errors in a BEGIN .. END block. Given I only want to ignore the foreign key violation when deleting from parent, that deletion and the WHEN GDSCODE foreign_key DO ... error handler must be in a nested BEGIN ... END block. When you'd use the other solution (delete with an EXISTS in the where clause), it wouldn't be necessary.

Mark