Mercurial > hg > orthanc-databases
comparison 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 |
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(); |