Last active
October 24, 2023 16:29
-
-
Save dongryphon/13d6aa71941e2a50d52f0fc8c87e6df6 to your computer and use it in GitHub Desktop.
Closure table triggers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- NOTE: Organizations have id and parentOrgId columns | |
-- OrgClosure has parentId, childId, depth columns | |
CREATE OR REPLACE TRIGGER Org_Add_Trigger | |
AFTER INSERT ON Organization | |
REFERENCING NEW AS NEW | |
FOR EACH ROW | |
BEGIN | |
-- must insert in this order... | |
insert into OrgClosure (parentId, childId, depth) | |
VALUES (NEW.id, NEW.id, 0); | |
insert into OrgClosure (parentId, childId, depth) | |
select p.parentId, c.childId, p.depth+c.depth+1 as depth | |
from OrgClosure p, OrgClosure c | |
where p.childId=NEW.parentOrgId and c.parentId=NEW.id; | |
END |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE TRIGGER Org_Delete_Trigger | |
AFTER DELETE ON Organization | |
REFERENCING OLD AS OLD | |
FOR EACH ROW | |
BEGIN | |
-- delete link | |
-- from OrgClosure p, OrgClosure link, OrgClosure c | |
-- where p.parentId = link.parentId and c.childId = link.childId | |
-- and p.childId = OLD.parentOrgId and c.parentId = OLD.id; | |
delete from OrgClosure as O where exists ( | |
SELECT 1 | |
FROM OrgClosure p, OrgClosure c | |
WHERE p.parentId = O.parentId AND c.childId = O.childId | |
AND p.childId = OLD.parentOrgId | |
AND c.parentId = OLD.id | |
); | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
If you don't mind, I didn't get the delete trigger?
can you explain?