Clearing Parent Item When Deleting Child

7 views
Skip to first unread message

Scott Morgan

unread,
Feb 17, 2021, 10:59:45 AMFeb 17
to firebird...@googlegroups.com
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

unread,
Feb 17, 2021, 11:19:56 AMFeb 17
to firebird...@googlegroups.com
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

unread,
Feb 17, 2021, 11:20:20 AMFeb 17
to firebird...@googlegroups.com
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

unread,
Feb 17, 2021, 11:38:32 AMFeb 17
to firebird...@googlegroups.com
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

unread,
Feb 17, 2021, 11:50:37 AMFeb 17
to firebird...@googlegroups.com
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

unread,
Feb 17, 2021, 12:13:18 PMFeb 17
to firebird...@googlegroups.com
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

unread,
Feb 17, 2021, 12:50:08 PMFeb 17
to firebird-support
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
Reply all
Reply to author
Forward
0 new messages