Here's my PLSQL trigger:
CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG
BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN
FOR EACH ROW
DECLARE
var_root number := :new.ROOT_ID;
var_par number := :new.UNIQUE_ID;
BEGIN
UPDATE UNIQUE_PHYSICIAN
SET ROOT_ID = var_root
WHERE PARENT_PHYS_ID = var_par;
END;
Will this trigger propagate? Like, if it updates a ROOT_ID for another record, will that trigger its own trigger? Further, if it does trigger that, will it use the new ROOT_ID? I want the ROOT_ID to propagate down the tree I've built.
Edit:
How this works is that each record has a unique ID, a parent ID, and a root ID. I basically have a tree, each member of that tree has a root_ID pointing at the unique ID of the root and a parent ID pointing at the one above it. The root's root and parent IDs are its own unique ID.
in the case that a user manually changes a record to point at a new root and parent, I want all the children of that node to have the new root ID. Is there a better way to do this?
root_id
for all the child rows? Why wouldn't the children just point at the parents and then query theroot_id
from the ultimate parent? How is aroot_id
different than aparent_id
? Is that just a node that doesn't have aparent_id
? If so, theroot_id
itself would seem redundant-- root nodes would be any node where theparent_id IS NULL
.