0

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?

4
  • Sounds like a recipe for the error: table is mutating, trigger may not see new state
    – kevinskio
    Commented Jun 22, 2015 at 17:15
  • Why is the table denormalized to store the root_id for all the child rows? Why wouldn't the children just point at the parents and then query the root_id from the ultimate parent? How is a root_id different than a parent_id? Is that just a node that doesn't have a parent_id? If so, the root_id itself would seem redundant-- root nodes would be any node where the parent_id IS NULL. Commented Jun 22, 2015 at 17:33
  • I want it to be such that someone can find all the records corresponding to a root by querying SELECT * FROM UNIQUE_PHYSICIAN WHERE ROOT_ID = 12345, which will show the record with UNIQUE_ID 12345 and all of it's children, grandchildren, etc. I considered not having a ROOT_ID, but I'm not sure how I would be able to retrieve the entire tree if I only had PARENT_ID. Would I be better off doing this with two tables? Commented Jun 22, 2015 at 17:35
  • What would I gain from having the root have a null parent and root ID, apart from making the query more complex? Commented Jun 22, 2015 at 17:40

3 Answers 3

2

My proposal would be this one:

CREATE TABLE UNIQUE_PHYSICIAN (
    PARENT_PHYS_ID NUMBER,
    PHYS_ID        NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY);

ALTER TABLE DPISYS.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK 
   FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS.UNIQUE_PHYSICIAN (PHYS_ID);

CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG 
    BEFORE UPDATE OF PHYS_ID ON UNIQUE_PHYSICIAN 
    FOR EACH ROW

BEGIN
    :NEW.PHYS_ID := :OLD.PHYS_ID;
END;

Then the root element(s) is/are the record(s) where PARENT_PHYS_ID IS NULL.

For PHYS_ID, resp. PARENT_PHYS_ID you should use a surrogate key, preferable generated from a sequence. In this case there is no reason (and no possibility) to change them ever.

The root Id you could get with this query:

SELECT p.*, CONNECT_BY_ROOT PHYS_ID AS ROOT_ID
FROM UNIQUE_PHYSICIAN p
START WITH PARENT_PHYS_ID IS NULL 
CONNECT BY PRIOR PHYS_ID = PARENT_PHYS_ID;
4
  • I'm confused. What would a query be that would retrieve an entire tree, from root to grand-grand-grand(etc) children with this structure? Further: :NEW.PHYS_ID := :OLD.PHYS_ID; What does this do? The point of what I'm doing is that by querying a root_id, I can get all of the results in that tree, because everything in the tree refers to both the parent and the root. I want to make it that when a parent node is assigned to a new root, all the children are assigned to that same root. This doesn't seem to use roots, and I don't really understand what it does at all. Could you elaborate? Commented Jun 23, 2015 at 13:38
  • I tried your suggestion, and the query you gave me just returns all the rows in the table. Commented Jun 23, 2015 at 13:55
  • Whoops! I understand now, the query is working. I had no idea sql had this functionality. I will try out the trigger. Would you mind explaining how this stuff is working? Commented Jun 23, 2015 at 14:00
  • :NEW.PHYS_ID := :OLD.PHYS_ID; means you cannot update this column. Any attempt to update it will keep the existing value. Commented Jun 23, 2015 at 16:00
0

Yes, if this was to work, it would generate an infinite loop. But more than likely, it will throw a mutating table exception first.

What is the problem that you are trying to solve? A trigger that updates other rows in a table when one row changes is unlikely to be a reasonable solution. If you have cross-row dependencies, that almost always indicates that you have a normalization issue that should be fixed in the data model rather than being coded around.

4
  • Would it generate an infinite loop? I want it, when I change a record's root, to have all the records with that record as their parent to have that root as well. The new update won't target the same row, right? Commented Jun 22, 2015 at 17:28
  • First, it would generate a mutating table exception. If you worked around the mutating table exception, it would presumably generate an infinite loop. If the data in one row is dependent upon the keys in a different row, you have a normalization problem that you should be solving. Commented Jun 22, 2015 at 17:30
  • How do I solve this normalization problem? This is what the table is for. Should I not have a tree in a database? How else could I represent this? Commented Jun 22, 2015 at 17:31
  • In some conditions you don't get the mutating table exception, see here: Why am I NOT getting a mutating table error in trigger? Commented Jun 23, 2015 at 11:37
0

If I understand your question correctly, if the trigger on YourTable inserts a row into YourTable as well, then that insertion will fire another instance of the same trigger.

See previous answers at: https://stackoverflow.com/questions/16391081/how-to-fix-oracle-trigger-causing-recursion-issue

Recursion has issues. Yes, I have written recursive code in the distant past, but I do not do it much any more. I suggest that it is often a bad idea to have triggers recursing.

Tree structures are just fine of course. Have you considered writing a stored procedure that would loop through the data set (without using recursion) and updating or inserting the rows appropriate to your goal?

2
  • I don't know how I would do this non-recursively. Whenever a row is changed, I want the children of that row is changed. When those children are changed, I want their children to change. Ad infinitum. Is it even possible to do this non-recursively? Commented Jun 23, 2015 at 13:46
  • Sounds like Wernfried may have resolved your problem. Regarding loops: if your environment did not support recursion you would have to write your own procedural code to update children, etc down the stack. It is quite doable.
    – RLF
    Commented Jun 23, 2015 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.