Index: openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql,v
diff -u -N -r1.3 -r1.4
--- openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql	24 Jun 2002 21:10:48 -0000	1.3
+++ openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql	28 Jun 2002 00:21:21 -0000	1.4
@@ -18,16 +18,16 @@
 before insert on dotlrn_community_types
 for each row
 declare
-    v_parent_sortkey dotlrn_community_types.tree_sortkey%TYPE;
-    v_max_child_sortkey dotlrn_community_types.max_child_sortkey%TYPE;
+    v_parent_sortkey                dotlrn_community_types.tree_sortkey%TYPE;
+    v_max_child_sortkey             dotlrn_community_types.max_child_sortkey%TYPE;
 begin
 
     if :new.supertype is null then
         -- if this is the root community_type we leave it's sortkey as null
         return;
     else
         -- else get the max_child_sortkey of the parent community_type
-        select tree_sortkey, max_child_sortkey
+        select nvl(tree_sortkey, ''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_community_types
         where community_type = :new.supertype
@@ -51,41 +51,40 @@
 before insert on dotlrn_communities_all
 for each row
 declare
-    v_parent_sortkey dotlrn_communities_all.tree_sortkey%TYPE;
-    v_max_child_sortkey dotlrn_communities_all.max_child_sortkey%TYPE;
+    v_parent_sortkey                dotlrn_communities_all.tree_sortkey%TYPE;
+    v_max_child_sortkey             dotlrn_communities_all.max_child_sortkey%TYPE;
 begin
 
     if :new.parent_community_id is null then
-        -- if this is the root community we get the sortkey from it's parent
-        -- community_type
-        select tree_sortkey, max_child_sortkey
+
+        select nvl(tree_sortkey, ''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_community_types
         where community_type = :new.community_type
         for update of max_child_sortkey;
+
+        v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
+
+        update dotlrn_community_types
+        set max_child_sortkey = v_max_child_sortkey
+        where community_type = :new.community_type;
+
     else
-        -- else get the max_child_sortkey of the parent community_type
-        select tree_sortkey, max_child_sortkey
+
+        select nvl(tree_sortkey, ''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_communities_all
         where community_id = :new.parent_community_id
         for update of max_child_sortkey;
-    end if;
 
-    -- increment the sort_key
-    v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
+        v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
 
-    if :new.parent_community_id is null then
-        update dotlrn_community_types
-        set max_child_sortkey = v_max_child_sortkey
-        where community_type = :new.community_type;
-    else
         update dotlrn_communities_all
         set max_child_sortkey = v_max_child_sortkey
         where community_id = :new.parent_community_id;
+
     end if;
 
-    -- generate the sortkey for the current row
     :new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;
 
 end dotlrn_communities_in_tr;
Index: openacs-4/packages/forums/sql/oracle/forums-tree-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-tree-create.sql,v
diff -u -N -r1.2 -r1.3
--- openacs-4/packages/forums/sql/oracle/forums-tree-create.sql	24 Jun 2002 21:10:46 -0000	1.2
+++ openacs-4/packages/forums/sql/oracle/forums-tree-create.sql	28 Jun 2002 00:14:16 -0000	1.3
@@ -13,50 +13,47 @@
 -- This is the sortkey code
 --
 
-
 create or replace trigger forums_mess_insert_tr
 before insert on forums_messages
 for each row
 declare
-        v_max_child_sortkey     forums_messages.max_child_sortkey%TYPE;
-        v_parent_sortkey        forums_messages.tree_sortkey%TYPE;
+    v_max_child_sortkey             forums_messages.max_child_sortkey%TYPE;
+    v_parent_sortkey                forums_messages.tree_sortkey%TYPE;
 begin
-   if :new.parent_id is null
-   then 
-        -- get the max from the forum
-        select max_child_sortkey into v_max_child_sortkey
-        from forums_forums where forum_id= :new.forum_id
+
+    if :new.parent_id is null
+    then
+
+        select '', max_child_sortkey
+        into v_parent_sortkey, v_max_child_sortkey
+        from forums_forums
+        where forum_id = :new.forum_id
         for update of max_child_sortkey;
 
-        v_parent_sortkey:= null;
-   else
-        -- get the max child sortkey from parent
-        -- grab the lock
-        select tree_sortkey, max_child_sortkey
+        v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
+
+        update forums_forums
+        set max_child_sortkey = v_max_child_sortkey
+        where forum_id = :new.forum_id;
+
+    else
+
+        select nvl(tree_sortkey, ''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from forums_messages
-        where message_id= :new.parent_id
+        where message_id = :new.parent_id
         for update of max_child_sortkey;
-   end if;
 
-   -- increment the sortkey
-   v_max_child_sortkey:= tree.increment_key(v_max_child_sortkey);
+        v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
 
-   if :new.parent_id is null
-   then
-      update forums_forums
-      set max_child_sortkey = v_max_child_sortkey
-      where forum_id= :new.forum_id;
-   else
-      -- update the parent
-      update forums_messages
-      set max_child_sortkey = v_max_child_sortkey
-      where message_id= :new.parent_id;
-   end if;
+        update forums_messages
+        set max_child_sortkey = v_max_child_sortkey
+        where message_id = :new.parent_id;
 
-   -- generate the current sortkey
-   :new.tree_sortkey:= v_parent_sortkey || v_max_child_sortkey;
-  
+    end if;
+
+    :new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;
+
 end forums_mess_insert_tr;
 /
 show errors
Index: openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql,v
diff -u -N -r1.5 -r1.6
--- openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql	25 Jun 2002 18:09:33 -0000	1.5
+++ openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql	28 Jun 2002 00:14:16 -0000	1.6
@@ -19,45 +19,38 @@
     v_max_child_sortkey             forums_forums.max_child_sortkey%TYPE;
     v_parent_sortkey                forums_messages.tree_sortkey%TYPE;
 begin
+
     if new.parent_id is null
-    then 
-        -- get the max from the forum
-        select max_child_sortkey
-        into v_max_child_sortkey
+    then
+
+        select '''', max_child_sortkey
+        into v_parent_sortkey, v_max_child_sortkey
         from forums_forums
         where forum_id = new.forum_id
         for update;
 
-        v_parent_sortkey = null;
+        v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
+
+        update forums_forums
+        set max_child_sortkey = v_max_child_sortkey
+        where forum_id = new.forum_id;
+
     else
-        -- get the max child sortkey from parent
-        -- grab the lock
-        select tree_sortkey, max_child_sortkey
+
+        select coalesce(tree_sortkey, ''''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from forums_messages
         where message_id = new.parent_id
         for update;
-    end if;
 
-    -- increment the sortkey
-    v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
+        v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
 
-    if new.parent_id is null
-    then
-        update forums_forums
-        set max_child_sortkey = v_max_child_sortkey
-        where forum_id = new.forum_id;
-    else
-        -- update the parent
         update forums_messages
         set max_child_sortkey = v_max_child_sortkey
         where message_id = new.parent_id;
-    end if;
 
-    -- generate the current sortkey
-    if v_parent_sortkey is null then
-        v_parent_sortkey := '''';
     end if;
+
     new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;
 
     return new;
@@ -66,4 +59,4 @@
 create trigger forums_mess_insert_tr
 before insert on forums_messages
 for each row
-execute procedure forums_mess_insert_tr ();
+execute procedure forums_mess_insert_tr();