--
-- packages/places/sql/places-create.sql
--
-- @author jmileham@arsdigita.com
-- @creation-date 2000-11-27
-- @cvs-id $Id: places-core-create.sql,v 1.1.1.1 2001/04/20 20:51:21 donb Exp $
--


------------------
-- PLACES TABLE --
------------------

create table places (
	place_id	not null
			constraint places_place_id_fk
			references acs_objects (object_id)
			constraint places_pk 
                        primary key,
	latitude	number(9,6),
	longitude	number(9,6)
);



--------------------------------
-- SUBPLACE AND LOCATION RELS --
--------------------------------

create table subplace_rels (
	rel_id		constraint subplace_rel_rel_id_fk
			references acs_rels (rel_id)
			constraint subplace_rel_rel_id_pk
			primary key
);

create table location_rels (
	rel_id		constraint location_rel_rel_id_fk
			references acs_rels (rel_id)
			constraint location_rel_rel_id_pk
			primary key,
	use_context	varchar2(30),
	sort_key	integer not null
);

-- NOTE: the subplace_rels and location_rels tables have triggers defined
--       at the end of this file.

--------------------
-- KNOWLEDGE LEVEL -
--------------------

begin


 --
 -- Place: an object representing a location
 -- Defining the object_type up here so we can use it in our relations
 --
 acs_object_type.create_type (
   supertype => 'acs_object',
   object_type=> 'place',
   pretty_name => 'Place',
   pretty_plural => 'Places',
   table_name => 'places',
   id_column => 'place_id',
   package_name => 'place'
 );



 --
 -- Subplace Relationship
 -- (transitive, to put places inside larger places)
 --

 acs_rel_type.create_role('superplace');
 acs_rel_type.create_role('subplace');

 acs_rel_type.create_type (
   rel_type => 'subplace_rel',
   pretty_name => 'Subplace Relation',
   pretty_plural => 'Subplace Relationships',
   table_name => 'subplace_rels',
   id_column => 'rel_id',
   package_name => 'subplace_rel',
   object_type_one => 'place', role_one => 'superplace',
   min_n_rels_one => 0, max_n_rels_one => null,
   object_type_two => 'place', role_two => 'subplace',
   min_n_rels_two => 0, max_n_rels_two => 1
 );

 --
 -- Location Relationship
 -- (not transitive, to assign places to objects)
 --


 acs_rel_type.create_role ('location');
 -- Yes, locatee is a real word, in the Oxford English Dictionary
 -- though this is the first time (that I know of) that it has
 -- been used to refer to an object instead of a person.
 acs_rel_type.create_role ('locatee');

 acs_rel_type.create_type (
   rel_type => 'location_rel',
   pretty_name => 'Location Relation',
   pretty_plural => 'Location Relationships',
   table_name => 'location_rels',
   id_column => 'rel_id',
   package_name => 'location_rel',
   object_type_one => 'place', role_one => 'location',
   min_n_rels_one => 0, max_n_rels_one => null,
   object_type_two => 'acs_object', role_two => 'locatee',
   min_n_rels_two => 0, max_n_rels_two => null
  );
 commit;
end;
/
show errors


-- We add a callback so that addresses can maintain the
-- pl_address_superplace_index denormalization when the
-- subplace_rels API is used.

create table place_types (
    place_type              constraint pt_place_type_fk
                            references acs_object_types(object_type)
                            constraint pt_place_type_pk
                            primary key,
    -- see comment below
    callback_set_superplace varchar(100)
);

comment on table place_types is '
 This table holds additional knowledge level attributes for the
 place type and its subtypes.  The way this metadata is currently
 used, these attributes are not "inherited" by subtypes.  You have
 to insert a row for each place_type if you want any of the attributes
 to have non-null values.  For example, if you subtype pl_address, then
 you must insert a row for your subtype in place_types, otherwise your
 subtype will not have a value for ''callback_set_superplace'', so 
 instances of your subtype will not behave the way addresses do.  Soon,
 we''ll have an API for creating place_types so that this table can be
 hidden from or ignored by developers.
';


comment on column place_types.callback_set_superplace is '
 This column stores the name of a pl/sql procedure to call whenever
 subplace_rels.new or subplace_rels.delete is used to set or clear
 the superplace of a place of type ''place_type''.
 For example, see pl_address.denormalize_superplaces
 \ 
 Note that the the callback is automatically used for the subtypes
 of ''place_type'' unless a callback has explicitly been set for the subtype.
 In other words, for a given place type, the callback that will be used
 is determined by finding the closest ancestor place type that has
 a non-null value for ''callback_set_superplace''.  This behavior resembles
 method inheritance.
';


-------------------------
-- PLACE ELEMENT INDEX --
-------------------------

-- This table is a denormalization for performance when answering
-- questions like "what are all the places in country X?" or
-- "what are all the objects located in MA?"
-- DO NOT READ OR WRITE TO THIS TABLE DIRECTLY

-- This maps any object to all places it is an element of.  
-- Including both subplace and location relationships.


create table place_element_index (
	place_id	not null
			constraint place_element_index_plc_id_fk
			references places (place_id),
	element_id	not null
			constraint place_element_index_elem_id_fk
			references acs_objects (object_id),
	rel_id		not null
			constraint place_element_index_rel_id_fk
			references acs_rels (rel_id),
	container_id	not null
			constraint place_element_index_cont_id_fk
			references places (place_id),
	subplace_p	char(1) not null
			constraint place_elem_index_comp_p_ck
			check (subplace_p in ('t','f')),
	constraint place_element_index_pk
	primary key (element_id, place_id, rel_id)
) organization index;

-------------------
-- WRAPPER VIEWS --
-------------------

-- The big momma view

create or replace view place_element_map
as select place_id, element_id, rel_id, container_id, subplace_p
     from place_element_index;

-- Just the subplaces:

create or replace view place_subplace_map
as select place_id, element_id as subplace_id, rel_id, container_id
     from place_element_map
    where subplace_p = 't';

-- Just the locations:

create or replace view place_locatee_map
as select place_id, element_id as locatee_id, rel_id, container_id
     from place_element_map
    where subplace_p = 'f';

-- This view is the same as place_locatee_map, including some extra
-- columns from location_rels.
create or replace view place_locatee_map_complete
as select plm.place_id, plm.locatee_id, plm.rel_id, plm.container_id,
          lr.use_context, lr.sort_key
     from place_locatee_map plm,
          location_rels lr
    where plm.rel_id = lr.rel_id;

--------------------------
-- SUBPLACE REL PACKAGE --
--------------------------

create or replace package subplace_rel
as

  function new (
    rel_id              in subplace_rels.rel_id%TYPE default null,
    rel_type            in acs_rels.rel_type%TYPE default 'subplace_rel',
    object_id_one       in acs_rels.object_id_one%TYPE,
    object_id_two       in acs_rels.object_id_two%TYPE,
    creation_user       in acs_objects.creation_user%TYPE default null,
    creation_ip         in acs_objects.creation_ip%TYPE default null
  ) return subplace_rels.rel_id%TYPE;

  function move (
    subplace_id		in place_subplace_map.subplace_id%TYPE,
    place_id_old	in place_subplace_map.place_id%TYPE,
    place_id_new	in place_subplace_map.place_id%TYPE,
    creation_user	in acs_objects.creation_user%TYPE default null,
    creation_ip		in acs_objects.creation_ip%TYPE default null
  ) return subplace_rels.rel_id%TYPE;

  function violation (
    place_id	in place_subplace_map.place_id%TYPE,
    subplace_id	in place_subplace_map.subplace_id%TYPE
  ) return varchar2;

  procedure delete (
    rel_id      in subplace_rels.rel_id%TYPE
  );

  procedure execute_callback (
  --/** 
  --    Run the set_superplace callback that is appropriate for the place_type
  --
  --    @param place_id The place whose superplace is being set.
  -- 
  --    @author Oumi Mehrotra (oumi@arsdigita.com)
  --    @creation-date 2001-03-19
  -- 
  --*/
    place_id          in places.place_id%TYPE
  );

end subplace_rel;
/
show errors

create or replace package body subplace_rel
as

  function new (
    rel_id              in subplace_rels.rel_id%TYPE default null,
    rel_type            in acs_rels.rel_type%TYPE default 'subplace_rel',
    object_id_one       in acs_rels.object_id_one%TYPE,
    object_id_two       in acs_rels.object_id_two%TYPE,
    creation_user       in acs_objects.creation_user%TYPE default null,
    creation_ip         in acs_objects.creation_ip%TYPE default null
  ) return subplace_rels.rel_id%TYPE
  is
    v_rel_id integer;
  begin
    v_rel_id := acs_rel.new (
      rel_id => rel_id,
      rel_type => rel_type,
      object_id_one => object_id_one,
      object_id_two => object_id_two,
      context_id => object_id_one,
      creation_user => creation_user,
      creation_ip => creation_ip
    );

    insert into subplace_rels
     (rel_id)
    values
     (v_rel_id);

    return v_rel_id;
  end new;

  function move (
    subplace_id		in place_subplace_map.subplace_id%TYPE,
    place_id_old	in place_subplace_map.place_id%TYPE,
    place_id_new	in place_subplace_map.place_id%TYPE,
    creation_user	in acs_objects.creation_user%TYPE default null,
    creation_ip		in acs_objects.creation_ip%TYPE default null
  ) return subplace_rels.rel_id%TYPE
  is
    rel_id_old	subplace_rels.rel_id%TYPE;
    v_rel_id	integer;
    cursor rel_id_c is
    select rel_id
      from place_subplace_map psm
     where subplace_id = move.subplace_id
       and container_id = move.place_id_old
       and container_id = place_id;
  begin
    open rel_id_c;
    fetch rel_id_c into rel_id_old;
    if not rel_id_c%NOTFOUND then
      if place_id_old = place_id_new then
	return rel_id_old;
      end if;
      subplace_rel.delete(rel_id_old);
    end if;
    close rel_id_c;
    if place_id_new is not null then
      v_rel_id := subplace_rel.new(object_id_one => place_id_new,
				   object_id_two => subplace_id,
				   creation_user => creation_user,
				   creation_ip => creation_ip);
    end if;

    return v_rel_id;
  end move;


  function violation (
    place_id	in place_subplace_map.place_id%TYPE,
    subplace_id	in place_subplace_map.subplace_id%TYPE
  ) return varchar2
  is
    subplace_p		char(1);
    complaint		varchar2(4000) null;
  begin

    -- Check if we've got a subplace_rel (be it direct or
    -- indirect) between place and subplace:

    select decode(count(*),0,'n','y')
      into subplace_p
      from place_subplace_map psm
     where psm.place_id = violation.place_id
       and psm.subplace_id = violation.subplace_id;

      -- and if we don't, complain:
    if subplace_p = 'n' then
      complaint := acs_object.name(subplace_id)||' is not within '||acs_object.name(place_id)||'.';
    end if;
    return complaint;
  end violation;


  procedure delete (
    rel_id      in subplace_rels.rel_id%TYPE
  )
  is
  begin
--    delete from subplace_rels
--    where rel_id = subplace_rel.delete.rel_id;
-- Handled by the acs_object.delete proc

    acs_object.delete(rel_id);
  end delete;

  procedure execute_callback (
  --/** 
  --    Run the set_superplace callback that is appropriate for the place_type
  --
  --    @param place_id The place whose superplace is being set.
  -- 
  --    @author Oumi Mehrotra (oumi@arsdigita.com)
  --    @creation-date 2001-03-19
  -- 
  --*/
    place_id          in places.place_id%TYPE
  )
  is
  begin

     for r in (select subplace_id as place_id, callback_set_superplace
               from (select place_id, 
                            subplace_id
                     from place_subplace_map
                     where place_id = execute_callback.place_id
                     union all
                     select execute_callback.place_id, 
                            execute_callback.place_id as subplace_id
                     from dual) psm,
                    acs_objects ao,
                    place_types pt
               where subplace_id = ao.object_id
                 and ao.object_type = pt.place_type
                 and pt.callback_set_superplace is not null) loop

         execute immediate 'begin ' || r.callback_set_superplace || '(:place_id); end;'
            using in r.place_id;

     end loop;

  end execute_callback;

end subplace_rel;
/
show errors

--------------------------
-- LOCATION REL PACKAGE --
--------------------------

create or replace package location_rel
as

  -- Instead of providing access to sort_key, .new takes a
  -- before_rel_id parameter.  If null, the new rel's sort_key
  -- will have a value greater than all other sort_keys associated
  -- with object_id_two (defaulting it to last).  If not null,
  -- location_rel.new causes the new rel's sort_key to have a
  -- value lower than that of the rel specified by before_rel_id.

  function new (
    rel_id              in location_rels.rel_id%TYPE default null,
    rel_type            in acs_rels.rel_type%TYPE default 'location_rel',
    object_id_one       in acs_rels.object_id_one%TYPE,
    object_id_two       in acs_rels.object_id_two%TYPE,
    creation_user       in acs_objects.creation_user%TYPE default null,
    creation_ip         in acs_objects.creation_ip%TYPE default null,
    use_context		in location_rels.use_context%TYPE default null,
    before_rel_id	in location_rels.rel_id%TYPE default null
  ) return location_rels.rel_id%TYPE;

  procedure swap_sort (
    rel_id_one		in location_rels.rel_id%TYPE,
    rel_id_two		in location_rels.rel_id%TYPE
  );

  function move (
    locatee_id		in place_locatee_map.locatee_id%TYPE,
    place_id_old	in place_locatee_map.place_id%TYPE,
    place_id_new	in place_locatee_map.place_id%TYPE,
    creation_user	in acs_objects.creation_user%TYPE default null,
    creation_ip		in acs_objects.creation_ip%TYPE default null
  ) return location_rels.rel_id%TYPE;

  procedure delete (
    rel_id      in location_rels.rel_id%TYPE
  );

end location_rel;
/
show errors

create or replace package body location_rel
as

  function new (
    rel_id              in location_rels.rel_id%TYPE default null,
    rel_type            in acs_rels.rel_type%TYPE default 'location_rel',
    object_id_one       in acs_rels.object_id_one%TYPE,
    object_id_two       in acs_rels.object_id_two%TYPE,
    creation_user       in acs_objects.creation_user%TYPE default null,
    creation_ip         in acs_objects.creation_ip%TYPE default null,
    use_context		in location_rels.use_context%TYPE default null,
    before_rel_id	in location_rels.rel_id%TYPE default null
  ) return location_rels.rel_id%TYPE
  is
    v_rel_id integer;
  begin
    v_rel_id := acs_rel.new (
      rel_id => rel_id,
      rel_type => rel_type,
      object_id_one => object_id_one,
      object_id_two => object_id_two,
      context_id => object_id_two,
      creation_user => creation_user,
      creation_ip => creation_ip
    );

    if new.before_rel_id is not null then

      -- If the rel_id doesn't exist that we're trying to insert
      -- before, bail out 'cause the application logic screwed
      -- up (non-explicit bail out - this will just cause
      -- a DB error if it isn't there) and shift all of the lower
      -- rows down to allow our new relation to take the
      -- target_sort_key:

      update location_rels lr
         set sort_key = sort_key + 1
       where sort_key >= (select sort_key
                            from location_rels
                           where rel_id = new.before_rel_id)
         and exists (select 1
                       from acs_rels
                      where rel_id = lr.rel_id
                        and object_id_two = new.object_id_two);

      -- And we're ready to insert into our newly created hole:

      insert into location_rels
        (rel_id, use_context, sort_key)
      select new.v_rel_id, new.use_context, (sort_key - 1)
        from location_rels
       where rel_id = new.before_rel_id;

    else

      -- If we're just inserting at the end of the list, we
      -- need a max.  Trust me... using a sequence would make
      -- the inserting sorting and/or deleting logic very hairy
      -- and inefficient.  Actually, a select max of a small subset
      -- of the rows (likely in the vicinity of 1-5%) in a table
      -- is reasonably low-overhead...  Certainly worth the ugliness
      -- and uncertainty that would be caused by having to increment
      -- the sequence every time, even if we don't use it. (OK
      -- if performance demands it, we'll change that down the
      -- line, it won't change API anyway).

      -- Note: may need to index location_rels on 
      -- (object_id_two, sort_key) to make this extra-speedy.

      insert into location_rels
        (rel_id, use_context, sort_key)
      select new.v_rel_id, new.use_context, nvl(max(sort_key) + 1, 1)
        from location_rels
       where object_id_two = new.object_id_two;

    end if;
   
    return v_rel_id;
  end new;

  procedure swap_sort (
    rel_id_one		in location_rels.rel_id%TYPE,
    rel_id_two		in location_rels.rel_id%TYPE
  )
  is
  begin


    -- The exists clause verifies that we're swapping two rels
    -- associated with the same object (otherwise the swap would
    -- be meaningless and dangerous to swap key uniqueness and
    -- integrity within an object).  If the swap is illegal, it
    -- will fail silently, but that's ok, cause it would take
    -- a big ol' bug in an application to try something like this
    -- in the first place.

    update location_rels lr1
       set sort_key = (select sort_key
                         from location_rels lr2
                        where lr1.rel_id = decode(lr2.rel_id,
                                           swap_sort.rel_id_one, swap_sort.rel_id_two,
                                           swap_sort.rel_id_two, swap_sort.rel_id_one))
     where rel_id in (swap_sort.rel_id_one, swap_sort.rel_id_two)
       and exists(select 1
		    from acs_rels ar1, acs_rels ar2
		   where ar1.rel_id = swap_sort.rel_id_one
		     and ar2.rel_id = swap_sort.rel_id_two
		     and ar1.object_id_two = ar2.object_id_two);

  end swap_sort;

  function move (
    locatee_id		in place_locatee_map.locatee_id%TYPE,
    place_id_old	in place_locatee_map.place_id%TYPE,
    place_id_new	in place_locatee_map.place_id%TYPE,
    creation_user	in acs_objects.creation_user%TYPE default null,
    creation_ip		in acs_objects.creation_ip%TYPE default null
  ) return location_rels.rel_id%TYPE
  is
    rel_id_old	location_rels.rel_id%TYPE;
    v_rel_id	integer;
    cursor rel_id_c is
    select rel_id
      from place_locatee_map plm
     where place_id = move.place_id_old
       and container_id = place_id
       and locatee_id = move.locatee_id;
  begin
    open rel_id_c;
    fetch rel_id_c into rel_id_old;
    if not rel_id_c%NOTFOUND then
      if place_id_old = place_id_new then
	return rel_id_old;
      end if;
      location_rel.delete(rel_id_old);
    end if;
    close rel_id_c;
    if place_id_new is not null then
      v_rel_id := location_rel.new(object_id_one => place_id_new,
				   object_id_two => locatee_id,
				   creation_user => creation_user,
				   creation_ip => creation_ip);
    end if;

    return v_rel_id;
  end move;

  procedure delete (
    rel_id      in location_rels.rel_id%TYPE
  )
  is
  begin
--    delete from location_rels
--    where rel_id = location_rel.delete.rel_id;
--  Handled by acs_object.delete

    acs_object.delete(rel_id);
  end delete;

end location_rel;
/
show errors


-------------------
-- PLACE PACKAGE --
-------------------

create or replace package place
as

 function new (
  place_id	in places.place_id%TYPE default null,
  object_type	in acs_objects.object_type%TYPE
		   default 'place',
  creation_date in acs_objects.creation_date%TYPE
		   default sysdate,
  creation_user in acs_objects.creation_user%TYPE
		   default null,
  creation_ip   in acs_objects.creation_ip%TYPE
		   default null,
  context_id	in acs_objects.context_id%TYPE default null,
  latitude	in places.latitude%TYPE default null,
  longitude	in places.longitude%TYPE default null,
  superplace_id	in acs_rels.object_id_one%TYPE default null,
  locatee_id	in acs_rels.object_id_two%TYPE default null,
  use_context	in location_rels.use_context%TYPE default null,
  before_rel_id	in location_rels.rel_id%TYPE default null
 ) return places.place_id%TYPE;

 procedure delete (
  place_id	in places.place_id%TYPE
 );

end place;
/
show errors

create or replace package body place
as

 function new (
  place_id	in places.place_id%TYPE default null,
  object_type	in acs_objects.object_type%TYPE
                   default 'place',
  creation_date in acs_objects.creation_date%TYPE
		   default sysdate,
  creation_user in acs_objects.creation_user%TYPE default null,
  creation_ip   in acs_objects.creation_ip%TYPE default null,
  context_id	in acs_objects.context_id%TYPE default null,
  latitude	in places.latitude%TYPE default null,
  longitude	in places.longitude%TYPE default null,
  superplace_id	in acs_rels.object_id_one%TYPE default null,
  locatee_id	in acs_rels.object_id_two%TYPE default null,
  use_context	in location_rels.use_context%TYPE default null,
  before_rel_id	in location_rels.rel_id%TYPE default null
 ) return places.place_id%TYPE
 is
  v_place_id	places.place_id%TYPE;
  v_rel_id	subplace_rels.rel_id%TYPE;
 begin
  v_place_id :=
   acs_object.new (
    object_id => place_id,
    object_type => object_type,
    creation_date => creation_date,
    creation_user => creation_user,
    creation_ip => creation_ip,
    context_id => context_id
   );

  insert into places
   (place_id, latitude, longitude)
  values
   (v_place_id, latitude, longitude);


  -- Note that wse don't allow the user
  -- to specify rel_types and stuff
  -- here.  If they want to get fancy,
  -- they can create the place and then
  -- associate it in a seperate step.
  -- This handles the most common use-case,
  -- though... where the user wants to create
  -- a place as a subplace of another and doesn't
  -- care too much about the metadata associated
  -- with it.

  if superplace_id is not null then
    v_rel_id :=
     subplace_rel.new(
      object_id_one => superplace_id,
      object_id_two => v_place_id,
      creation_user => creation_user,
      creation_ip => creation_ip
     );
  end if;

  -- Location relationship is a little fancier, so
  -- we let them specify a use_context and a before_rel_id
  -- if they so choose.

  if locatee_id is not null then
    v_rel_id :=
     location_rel.new(
      object_id_one => v_place_id,
      object_id_two => locatee_id,
      creation_user => creation_user,
      creation_ip => creation_ip,
      use_context => use_context,
      before_rel_id => before_rel_id);
  end if;

  return v_place_id;
 end new;

 procedure delete (
  place_id	in places.place_id%TYPE
 )
 is
 begin
  -- First delete any subplace rels for which this place is object two
  -- i.e. the subplace

  for map in (select *
		from place_subplace_map
	       where subplace_id = place.delete.place_id
	  	 and place_id = container_id) loop
    subplace_rel.delete(map.rel_id);
  end loop;

  acs_object.delete(place_id);
 end delete;

end place;
/
show errors



-----------------------------------
-- SUBPLACE_RELS INSERT TRIGGERS --
-----------------------------------

create or replace trigger subplace_rels_in_tr
after insert on subplace_rels
for each row
declare
  v_object_id_one acs_rels.object_id_one%TYPE;
  v_object_id_two acs_rels.object_id_two%TYPE;
begin
  select object_id_one, object_id_two
    into v_object_id_one, v_object_id_two
    from acs_rels
   where rel_id = :new.rel_id;


  -- extend any relations between the subplace and it's children to the place and all of its
  -- ancestors

  insert into place_element_index
   (place_id, element_id, rel_id, container_id, subplace_p)
  select super.place_id, sub.element_id, sub.rel_id, 
         sub.container_id, sub.subplace_p
    from (select place_id
          from place_subplace_map 
          where subplace_id = v_object_id_one
           union
          select v_object_id_one as place_id from dual) super,
         (select element_id, rel_id, container_id, subplace_p 
          from place_element_map  
          where place_id = v_object_id_two
           union
          select v_object_id_two as element_id, :new.rel_id as rel_id, 
                 v_object_id_one as container_id, 't' as subplace_p 
          from dual) sub;

  subplace_rel.execute_callback(v_object_id_two);

end subplace_rels_in_tr;
/
show errors


----------------------------------
-- SUBPLACE_RELS DELETE TRIGGER --
----------------------------------

create or replace trigger subplace_rels_del_tr
before delete on subplace_rels
for each row
declare
  v_object_id_one acs_rels.object_id_one%TYPE;
  v_object_id_two acs_rels.object_id_two%TYPE;
begin
  select object_id_one, object_id_two into v_object_id_one, v_object_id_two
  from acs_rels
  where rel_id = :old.rel_id;

  delete from place_element_index
   where element_id in (select element_id
                          from place_element_map
                         where place_id = v_object_id_one)
     and place_id in (select place_id
			from place_element_map
		       where element_id = v_object_id_two);

  subplace_rel.execute_callback(v_object_id_two);

end subplace_rels_del_tr;
/
show errors


-----------------------------
-- LOCATION INSERT TRIGGER --
-----------------------------

create or replace trigger location_rels_in_tr
after insert on location_rels
for each row
declare
  v_object_id_one acs_rels.object_id_one%TYPE;
  v_object_id_two acs_rels.object_id_two%TYPE;
begin
  select object_id_one, object_id_two
    into v_object_id_one, v_object_id_two
    from acs_rels
   where rel_id = :new.rel_id;

  -- Insert a row in the place_element_index
  insert into place_element_index
   (place_id, element_id, rel_id, container_id,
    subplace_p)
  values
   (v_object_id_one, v_object_id_two, :new.rel_id, v_object_id_one, 'f');

  -- For all places that my newly defined "location" is a subplace of,
  -- insert a row in place_element_index
  for map in (select distinct place_id
                from place_subplace_map
               where subplace_id = v_object_id_one) loop
    insert into place_element_index
     (place_id, element_id, rel_id, container_id, subplace_p)
    values
     (map.place_id, v_object_id_two, :new.rel_id, v_object_id_one, 'f');
  end loop;
end location_rels_in_tr;
/
show errors


-----------------------------
-- LOCATION DELETE TRIGGER --
-----------------------------

create or replace trigger location_rels_del_tr
before delete on location_rels
for each row
begin
  delete from place_element_index
  where rel_id = :old.rel_id;
end location_rels_del_tr;
/
show errors;


