0

I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too.

At the same table I have a boolean column active, is it possible to use ON UPDATE CASCADE in order to make the update of this column impact recursively all the children of the updated row?

let's take this example parent > sub1 > sub2, what is the way (at the database level) to make the update of the column active impact all the children?

  • update the parent will impact himself and sub1 and sub2
  • update the sub1 will impact himself and the sub2.

and so on.

Thanks in advance.

Improve this question

1 Answer 1

Reset to default
0

I've never used Postgres so can't go into specifics but it looks like you'd be ab;e to achieve your aims by using a trigger that fires on update of the Active column. Set the function to check for any child records and update any found. The trigger should recurse through all levels until it finds a record with no children. Postgres Trigger Functions.

Improve this answer
2
  • Thank you for the advice, I really want to avoid the trigger method. It will be nice if the update can be cast automatically one by one like the delete is.
    – bguernouti
    Commented Jun 14, 2023 at 19:47
  • It doesn't look like there's an inbuilt method of cascading "soft deletes". If you don't want to use triggers, I try to avoid them, too, you're probably stuck with having to handle these updates in a stored procedure that checks for child records on update or handling it in application code.
    – JHackney
    Commented Jun 15, 2023 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.