Skip to content

Instantly share code, notes, and snippets.

@dongryphon
Last active October 24, 2023 16:29
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save dongryphon/13d6aa71941e2a50d52f0fc8c87e6df6 to your computer and use it in GitHub Desktop.
Save dongryphon/13d6aa71941e2a50d52f0fc8c87e6df6 to your computer and use it in GitHub Desktop.
Closure table triggers
-- 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
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
@adshin21
Copy link

If you don't mind, I didn't get the delete trigger?
can you explain?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment