Is the AFTER UPDATE trigger really needed?

[amaury1093]

django-ltree-demo/demo/categories/sql/triggers.sql

Lines 48 to 54 in 5752252

	-- if the path was updated, update the path of the descendants
	DROP TRIGGER IF EXISTS category_path_after_trg ON categories_category;
	CREATE TRIGGER category_path_after_trg
	AFTER UPDATE ON categories_category
	FOR EACH ROW
	WHEN (NEW.path IS DISTINCT FROM OLD.path)
	EXECUTE PROCEDURE _update_descendants_category_path();

I can't get my head around why the above trigger is needed. Imo it will do the same job again as the BEFORE UPDATE trigger.

Can you provide an example where the AFTER does something different than the BEFORE?

[k4nar]

This is the case covered by this test:

django-ltree-demo/tests/test_ltree.py

Lines 126 to 153 in 741de07

	def test_update_parent():
	top = Category.objects.create(code='top')
	top.refresh_from_db()
	Category.objects.create(code='sport', parent=top)
	science = Category.objects.create(code='science', parent=top)
	biology = Category.objects.create(code='biology', parent=science)
	Category.objects.create(code='genetics', parent=biology)
	Category.objects.create(code='neuroscience', parent=biology)
	# update the parent of a category, it should update its path as well as
	# the path of all of its descendants
	biology.parent = top
	biology.save()
	assert list(
	Category.objects
	.filter(path__descendant=top.path)
	.values_list('path', flat=True)
	.order_by('path')
	) == [
	'top',
	'top.biology',
	'top.biology.genetics',
	'top.biology.neuroscience',
	'top.science',
	'top.sport',
	]

. Basically this happens every time you change the parent of a row which has descendants.

The trigger category_path_update_trg only updates the path of the current row, but we need to update the path of all the descendants as well. This is what category_path_after_trg does.

[amaury1093]

💯