1 -- Evergreen DB patch 0580.schema.aou_parent_protec.sql
7 -- check whether patch can be applied
8 SELECT evergreen.upgrade_deps_block_check('0580', :eg_version);
10 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
12 current_aou actor.org_unit%ROWTYPE;
18 seen_ous := ARRAY[NEW.id];
19 IF TG_OP = 'INSERT' OR NEW.parent_ou IS DISTINCT FROM OLD.parent_ou THEN
21 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
22 RETURN NEW; -- No loop. Carry on.
24 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
25 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
28 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
29 seen_ous := seen_ous || current_aou.id;
30 depth_count := depth_count + 1;
31 IF depth_count = 100 THEN
32 RAISE 'OU CHECK TOO DEEP';
40 CREATE TRIGGER actor_org_unit_parent_protect_trigger
41 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
42 EXECUTE PROCEDURE actor.org_unit_parent_protect ();