Index: openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql	23 Feb 2002 02:43:45 -0000	1.3
+++ openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql	18 Feb 2003 20:53:45 -0000	1.4
@@ -10,6 +10,30 @@
 -- TRIGGERS --
 --------------
 
+create or replace trigger membership_rels_up_tr
+before update on membership_rels
+for each row
+begin
+  
+  if :new.member_state = :old.member_state then
+    return;
+  end if;
+
+  for map in (select group_id, element_id, rel_type
+              from group_element_index
+              where rel_id = :new.rel_id)
+  loop
+    if :new.member_state = 'approved' then
+      party_approved_member.add(map.group_id, map.element_id, map.rel_type);
+    else
+      party_approved_member.remove(map.group_id, map.element_id, map.rel_type);
+    end if;
+  end loop;
+
+end;
+/
+show errors
+
 create or replace trigger membership_rels_in_tr
 after insert on membership_rels
 for each row
@@ -39,6 +63,10 @@
    (v_object_id_one, v_object_id_two, :new.rel_id, v_object_id_one, 
     v_rel_type, 'membership_rel');
 
+  if :new.member_state = 'approved' then
+    party_approved_member.add(v_object_id_one, v_object_id_two, v_rel_type);
+  end if;
+
   -- For all groups of which I am a component, insert a
   -- row in the group_member_index.
   for map in (select distinct group_id
@@ -50,11 +78,42 @@
     values
      (map.group_id, v_object_id_two, :new.rel_id, v_object_id_one,
       v_rel_type, 'membership_rel');
+
+    if :new.member_state = 'approved' then
+      party_approved_member.add(map.group_id, v_object_id_two, v_rel_type);
+    end if;
+
   end loop;
 end;
 /
 show errors
 
+create or replace trigger membership_rels_del_tr
+before delete on membership_rels
+for each row
+declare 
+  v_error varchar2(4000);
+begin
+  -- First check if removing this relation would violate any relational constraints
+  v_error := rel_constraint.violation_if_removed(:old.rel_id);
+  if v_error is not null then
+      raise_application_error(-20000,v_error);
+  end if;
+
+  for map in (select group_id, element_id, rel_type
+              from group_element_index
+              where rel_id = :old.rel_id)
+  loop
+    party_approved_member.remove(map.group_id, map.element_id, map.rel_type);
+  end loop;
+
+  delete from group_element_index
+  where rel_id = :old.rel_id;
+
+end;
+/
+show errors;
+
 create or replace trigger composition_rels_in_tr
 after insert on composition_rels
 for each row
@@ -133,24 +192,6 @@
 /
 show errors
 
-create or replace trigger membership_rels_del_tr
-before delete on membership_rels
-for each row
-declare 
-  v_error varchar2(4000);
-begin
-  -- First check if removing this relation would violate any relational constraints
-  v_error := rel_constraint.violation_if_removed(:old.rel_id);
-  if v_error is not null then
-      raise_application_error(-20000,v_error);
-  end if;
-
-  delete from group_element_index
-  where rel_id = :old.rel_id;
-end;
-/
-show errors;
-
 --
 -- TO DO: See if this can be optimized now that the member and component
 -- mapping tables have been combined