Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 432:8b7c1c423367 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
431:7c1fe5d6c12c 432:8b7c1c423367
1 -- this script can be used either the first time we create the DB or during an upgrade
2 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
3
4 -- The following trigger combines 2 triggers from SQLite:
5 -- ResourceDeleted + ResourceDeletedParentCleaning
6 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
7 RETURNS TRIGGER AS $body$
8 BEGIN
9 --RAISE NOTICE 'Delete resource %', old.parentId;
10 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
11
12 -- If this resource still has siblings, keep track of the remaining parent
13 -- (a parent that must not be deleted but whose LastUpdate must be updated)
14 INSERT INTO RemainingAncestor SELECT resourceType, publicId
15 FROM Resources
16 WHERE internalId = old.parentId
17 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
18 -- If this resource is the latest child, delete the parent
19 DELETE FROM Resources WHERE internalId = old.parentId
20 AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
21 RETURN NULL;
22 END;
23 $body$ LANGUAGE plpgsql;
24
25 CREATE TRIGGER ResourceDeleted
26 AFTER DELETE ON Resources
27 FOR EACH ROW
28 EXECUTE PROCEDURE ResourceDeletedFunc();