Mercurial > hg > orthanc-databases
diff PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 432:8b7c1c423367 pg-transactions
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
author | Alain Mazy <am@osimis.io> |
---|---|
date | Mon, 11 Dec 2023 14:39:27 +0100 |
parents | |
children | 5964ce6385a5 |
line wrap: on
line diff
--- /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();