Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

experience ppl. could you help on StoreProc/Trigger???

WingManWingMan Member Posts: 4
Hi...This question might be easy for some of you out there, but i am having a difficult problem with it. I have a tree structure with the parent node being the "propertyID" and the leaf nodes are the "propertyType" associated with the propertyID.

How can i create a Store Procedure that would prints a list of leaf nodes given the propertyID? Also, how can i create a trigger that would delete a sub-tree below a node, and back up that sub-tree including the node where it trigger the deletion, onto a back_up table?

Very challenging...can someone help or good hints???

Comments

  • infidelinfidel Member Posts: 2,900
    : Hi...This question might be easy for some of you out there, but i am having a difficult problem with it. I have a tree structure with the parent node being the "propertyID" and the leaf nodes are the "propertyType" associated with the propertyID.
    :
    : How can i create a Store Procedure that would prints a list of leaf nodes given the propertyID? Also, how can i create a trigger that would delete a sub-tree below a node, and back up that sub-tree including the node where it trigger the deletion, onto a back_up table?
    :
    : Very challenging...can someone help or good hints???

    Are you saying you have a single table or two tables? Please describe your table structure.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • WingManWingMan Member Posts: 4
    : : Hi...This question might be easy for some of you out there, but i am having a difficult problem with it. I have a tree structure with the parent node being the "propertyID" and the leaf nodes are the "propertyType" associated with the propertyID.
    : :
    : : How can i create a Store Procedure that would prints a list of leaf nodes given the propertyID? Also, how can i create a trigger that would delete a sub-tree below a node, and back up that sub-tree including the node where it trigger the deletion, onto a back_up table?
    : :
    : : Very challenging...can someone help or good hints???
    :
    : Are you saying you have a single table or two tables? Please describe your table structure.
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :
    --------------------------------------------
    I am so glad someone took an interest in replying. It is one table with the tree structure. The schema is
    PropertyType(propertyID, propertyName, parentPropertyID).
    This is the tree structure for the table:

    root: a -> b,c,d
    b -> e,f
    d -> g,h,i
    e -> j,k,l
    f -> m,n
    h -> o,p,q
    i -> r
    k -> s,t,u
    l -> v
    n -> y,x

    The parameter that passes in is PropertyType(propertyID) and the result of the StoreProcedure returns a list of leaf nodes(propertyName) for the given input. Incase i didn't explain the tree structure very well, these are some of the data store in the PropertyType table:
    PROPERTYID PROPERTYNAME PARENTPROPERTYID
    ------------- -------------------- -------------------
    1 a
    2 b 1
    3 c 1
    4 d 1
    5 e 2
    6 f 2
    7 g 4
    8 h 4
    9 i 4
    10 j 5
    11 k 5
    12 l 5
    .
    .
    .
  • infidelinfidel Member Posts: 2,900
    [b][red]This message was edited by infidel at 2003-12-5 8:31:1[/red][/b][hr]
    [b][red]This message was edited by infidel at 2003-12-5 8:18:46[/red][/b][hr]
    : : : Hi...This question might be easy for some of you out there, but i am having a difficult problem with it. I have a tree structure with the parent node being the "propertyID" and the leaf nodes are the "propertyType" associated with the propertyID.
    : : :
    : : : How can i create a Store Procedure that would prints a list of leaf nodes given the propertyID? Also, how can i create a trigger that would delete a sub-tree below a node, and back up that sub-tree including the node where it trigger the deletion, onto a back_up table?
    : : :
    : : : Very challenging...can someone help or good hints???
    : :
    : : Are you saying you have a single table or two tables? Please describe your table structure.
    : :
    : : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    : :
    : :
    : --------------------------------------------
    : I am so glad someone took an interest in replying. It is one table with the tree structure. The schema is
    : PropertyType(propertyID, propertyName, parentPropertyID).
    : This is the tree structure for the table:
    :
    : root: a -> b,c,d
    : b -> e,f
    : d -> g,h,i
    : e -> j,k,l
    : f -> m,n
    : h -> o,p,q
    : i -> r
    : k -> s,t,u
    : l -> v
    : n -> y,x
    :
    : The parameter that passes in is PropertyType(propertyID) and the result of the StoreProcedure returns a list of leaf nodes(propertyName) for the given input. Incase i didn't explain the tree structure very well, these are some of the data store in the PropertyType table:
    : PROPERTYID PROPERTYNAME PARENTPROPERTYID
    : ------------- -------------------- -------------------
    : 1 a
    : 2 b 1
    : 3 c 1
    : 4 d 1
    : 5 e 2
    : 6 f 2
    : 7 g 4
    : 8 h 4
    : 9 i 4
    : 10 j 5
    : 11 k 5
    : 12 l 5

    Ok, I don't yet have an idea for printing the leaf nodes, but here's a thought for backing up and deleting a subtree:

    [code]
    procedure backup_and_delete_subtree (
    i_subtree_root varchar2
    ) is
    cursor x_subtree_nodes is
    select propertyid, propertyname, parentpropertyid,
    level, sys_connect_by_path(propertyname, '/') path
    from propertytype
    start with propertyname = i_subtree_root
    connect by prior id = parent_id
    order by path desc;
    begin
    for r_subtree_node in x_subtree_nodes loop
    insert into propertytype_backup values (
    r_subtree_node.propertyid,
    r_subtree_node.propertyname,
    r_subtree_node.parentpropertyid
    );
    delete from propertytype
    where propertyid = r_subtree_node.propertyid;
    end loop;
    end;
    [/code]

    The query used in the cursor is worth pulling out and experimenting with to get a feel for hierarchical selects. Once you understand the query and the way I sorted it, it should be obvious how the rest of the procedure works. As long as the main table has something like a sequence generating the propertyid values (so they never repeat), this should be an ok backup methodology.

    I don't recommend doing this within a delete trigger because every time you try to delete a child node, the trigger will fire again. Risky business if you ask me. You could, however, do the backing up of a record within a delete trigger. That would be safe.

    I don't know if you can put a foreign key on a table that references the same table and use cascading deletes to kill entire subtrees with one delete statement. Would be worth experimenting with, IMO.

    Bear in mind I am using Oracle9i, so if you have an earlier version this may not work.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]





  • WingManWingMan Member Posts: 4
    : [b][red]This message was edited by infidel at 2003-12-5 8:31:1[/red][/b][hr]
    : [b][red]This message was edited by infidel at 2003-12-5 8:18:46[/red][/b][hr]
    : : : : Hi...This question might be easy for some of you out there, but i am having a difficult problem with it. I have a tree structure with the parent node being the "propertyID" and the leaf nodes are the "propertyType" associated with the propertyID.
    : : : :
    : : : : How can i create a Store Procedure that would prints a list of leaf nodes given the propertyID? Also, how can i create a trigger that would delete a sub-tree below a node, and back up that sub-tree including the node where it trigger the deletion, onto a back_up table?
    : : : :
    : : : : Very challenging...can someone help or good hints???
    : : :
    : : : Are you saying you have a single table or two tables? Please describe your table structure.
    : : :
    : : : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    : : :
    : : :
    : : --------------------------------------------
    : : I am so glad someone took an interest in replying. It is one table with the tree structure. The schema is
    : : PropertyType(propertyID, propertyName, parentPropertyID).
    : : This is the tree structure for the table:
    : :
    : : root: a -> b,c,d
    : : b -> e,f
    : : d -> g,h,i
    : : e -> j,k,l
    : : f -> m,n
    : : h -> o,p,q
    : : i -> r
    : : k -> s,t,u
    : : l -> v
    : : n -> y,x
    : :
    : : The parameter that passes in is PropertyType(propertyID) and the result of the StoreProcedure returns a list of leaf nodes(propertyName) for the given input. Incase i didn't explain the tree structure very well, these are some of the data store in the PropertyType table:
    : : PROPERTYID PROPERTYNAME PARENTPROPERTYID
    : : ------------- -------------------- -------------------
    : : 1 a
    : : 2 b 1
    : : 3 c 1
    : : 4 d 1
    : : 5 e 2
    : : 6 f 2
    : : 7 g 4
    : : 8 h 4
    : : 9 i 4
    : : 10 j 5
    : : 11 k 5
    : : 12 l 5
    :
    : Ok, I don't yet have an idea for printing the leaf nodes, but here's a thought for backing up and deleting a subtree:
    :
    : [code]
    : procedure backup_and_delete_subtree (
    : i_subtree_root varchar2
    : ) is
    : cursor x_subtree_nodes is
    : select propertyid, propertyname, parentpropertyid,
    : level, sys_connect_by_path(propertyname, '/') path
    : from propertytype
    : start with propertyname = i_subtree_root
    : connect by prior id = parent_id
    : order by path desc;
    : begin
    : for r_subtree_node in x_subtree_nodes loop
    : insert into propertytype_backup values (
    : r_subtree_node.propertyid,
    : r_subtree_node.propertyname,
    : r_subtree_node.parentpropertyid
    : );
    : delete from propertytype
    : where propertyid = r_subtree_node.propertyid;
    : end loop;
    : end;
    : [/code]
    :
    : The query used in the cursor is worth pulling out and experimenting with to get a feel for hierarchical selects. Once you understand the query and the way I sorted it, it should be obvious how the rest of the procedure works. As long as the main table has something like a sequence generating the propertyid values (so they never repeat), this should be an ok backup methodology.
    :
    : I don't recommend doing this within a delete trigger because every time you try to delete a child node, the trigger will fire again. Risky business if you ask me. You could, however, do the backing up of a record within a delete trigger. That would be safe.
    :
    : I don't know if you can put a foreign key on a table that references the same table and use cascading deletes to kill entire subtrees with one delete statement. Would be worth experimenting with, IMO.
    :
    : Bear in mind I am using Oracle9i, so if you have an earlier version this may not work.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :
    :
    ----------------------------------------------------


    In the cursor SELECT statement:
    [code]
    SELECT propertyid, propertyname, parentpropertyid,
    level, sys_connect_by_path(propertyname, '/') path
    [/code]
    What is the level, and sys_connect_by_path(propertyname, '/')path?
    Is this a system function, how does it actually work?


    [italic]
    Ok, i also have a thought on printing a list of leaf nodes, but i am not exactly sure on how to implement this algorithm using PL/SQL.
    Here is somewhat of a psuedocode:
    [/italic]
    [code]
    user's input propertyID
    LOOP
    CHECK to see if it's in parentpropertyID
    IF not found in parentpropertyID print(propertyName)
    ELSE recursively check the rows between propertyID
    and parentpropertyID, starting from the user's input
    until you reach the rows where propertyID is
    not found in parentpropertyID.
    then print(propertyName) of those rows.
    END LOOP
    [/code]

    I am not sure if my logic is correct here, but i figured starting from the user input of propertyID, the procedure would need to checks the rows against the parentpropertyID. Running through the table in that manner until it reaches the rows that doesn't reference to the parentpropertyID, which means those rows are the leaf nodes from the user's input and print the propertyName of those rows. Now, if this is correct, could you translate this into PL/SQL syntax?

    Sorry if my explaination seem to be a little fuzzy...I tried my best to make sense.



  • infidelinfidel Member Posts: 2,900
    : In the cursor SELECT statement:
    : [code]
    : SELECT propertyid, propertyname, parentpropertyid,
    : level, sys_connect_by_path(propertyname, '/') path
    : [/code]
    : What is the level, and sys_connect_by_path(propertyname, '/')path?
    : Is this a system function, how does it actually work?

    Yes they are system functions. If you run the query on your data you should see how they work. If you have a tree like:

    [code]
    a
    /
    b c
    /
    d e
    [/code]

    Then a would be level 1, b and c would be level 2, d and e would be level 3. The function sys_connect_by_path(propertyname, '/') would return /a for a, /a/b and /a/c for b and c, and /a/b/d and /a/b/e for d an d, just like a directory path.

    : [italic]
    : Ok, i also have a thought on printing a list of leaf nodes, but i am not exactly sure on how to implement this algorithm using PL/SQL.
    : Here is somewhat of a psuedocode:
    : [/italic]
    : [code]
    : user's input propertyID
    : LOOP
    : CHECK to see if it's in parentpropertyID
    : IF not found in parentpropertyID print(propertyName)
    : ELSE recursively check the rows between propertyID
    : and parentpropertyID, starting from the user's input
    : until you reach the rows where propertyID is
    : not found in parentpropertyID.
    : then print(propertyName) of those rows.
    : END LOOP
    : [/code]
    :
    : I am not sure if my logic is correct here, but i figured starting from the user input of propertyID, the procedure would need to checks the rows against the parentpropertyID. Running through the table in that manner until it reaches the rows that doesn't reference to the parentpropertyID, which means those rows are the leaf nodes from the user's input and print the propertyName of those rows. Now, if this is correct, could you translate this into PL/SQL syntax?
    :
    : Sorry if my explaination seem to be a little fuzzy...I tried my best to make sense.

    That might work, but I don't have time to test it. I've got an idea for a pl/sql solution. If I have a chance to play with it I'll let you know.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

Sign In or Register to comment.