--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/ResourceDeletedFunc.sql	Mon Dec 11 14:39:27 2023 +0100
@@ -0,0 +1,28 @@
+-- this script can be used either the first time we create the DB or during an upgrade
+DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
+
+-- The following trigger combines 2 triggers from SQLite:
+-- ResourceDeleted + ResourceDeletedParentCleaning
+CREATE OR REPLACE FUNCTION ResourceDeletedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  --RAISE NOTICE 'Delete resource %', old.parentId;
+  INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
+  
+  -- If this resource still has siblings, keep track of the remaining parent
+  -- (a parent that must not be deleted but whose LastUpdate must be updated)
+  INSERT INTO RemainingAncestor SELECT resourceType, publicId 
+                                FROM Resources 
+                                WHERE internalId = old.parentId
+                                      AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
+  -- If this resource is the latest child, delete the parent
+  DELETE FROM Resources WHERE internalId = old.parentId
+                              AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE TRIGGER ResourceDeleted
+AFTER DELETE ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE ResourceDeletedFunc();