Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 433:5964ce6385a5 pg-transactions
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
author | Alain Mazy <am@osimis.io> |
---|---|
date | Wed, 13 Dec 2023 15:48:56 +0100 |
parents | 8b7c1c423367 |
children | 23c7af6f671a |
comparison
equal
deleted
inserted
replaced
432:8b7c1c423367 | 433:5964ce6385a5 |
---|---|
9 --RAISE NOTICE 'Delete resource %', old.parentId; | 9 --RAISE NOTICE 'Delete resource %', old.parentId; |
10 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); | 10 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); |
11 | 11 |
12 -- If this resource still has siblings, keep track of the remaining parent | 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) | 13 -- (a parent that must not be deleted but whose LastUpdate must be updated) |
14 INSERT INTO RemainingAncestor SELECT resourceType, publicId | 14 INSERT INTO RemainingAncestor SELECT resourceType, publicId |
15 FROM Resources | 15 FROM Resources |
16 WHERE internalId = old.parentId | 16 WHERE internalId = old.parentId |
17 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); | 17 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); |
18 -- If this resource is the latest child, delete the parent | 18 -- If this resource is the latest child, delete the parent |
19 DELETE FROM Resources WHERE internalId = old.parentId | 19 DELETE FROM Resources WHERE internalId = old.parentId |
24 | 24 |
25 CREATE TRIGGER ResourceDeleted | 25 CREATE TRIGGER ResourceDeleted |
26 AFTER DELETE ON Resources | 26 AFTER DELETE ON Resources |
27 FOR EACH ROW | 27 FOR EACH ROW |
28 EXECUTE PROCEDURE ResourceDeletedFunc(); | 28 EXECUTE PROCEDURE ResourceDeletedFunc(); |
29 | |
30 -- we'll now use temporary tables so we need to remove the old tables ! | |
31 DROP TABLE IF EXISTS DeletedFiles; | |
32 DROP TABLE IF EXISTS RemainingAncestor; | |
33 DROP TABLE IF EXISTS DeletedResources; |