create or replace trigger sn_object_types_in_tr
after insert on sn_object_types
for each row
begin
  acs_object.new_description(
    v_object_id => :new.object_type_id,
    v_shortname	=> :new.pretty_name,
    v_package_id => :new.context_id);
end sn_objects_in_tr;
/
show errors

create or replace trigger sn_object_types_up_tr
after update on sn_object_types
for each row
declare
begin
  acs_object.set_shortname(
    v_object_id => :new.object_type_id,
    v_shortname	=> :new.pretty_name);

  acs_object.set_package_id(
    v_object_id => :new.object_type_id,
    v_package_id => :new.context_id);

end sn_object_types_up_tr;
/
show errors


create or replace trigger sn_objects_in_tr
after insert on sn_objects
for each row
declare
  v_new_one_line_description sn_objects.one_line_description%TYPE;
  v_creation_date acs_objects.creation_date%TYPE;
begin
  if :new.one_line_description is null or length(trim(:new.one_line_description)) = 0 then
    select 'Unnamed ' || pretty_name
    into v_new_one_line_description
    from sn_object_types
    where object_type_id = :new.object_type_id;
  else 
    v_new_one_line_description := :new.one_line_description;
  end if;
 
  select creation_date into v_creation_date
  from acs_objects
  where object_id = :new.object_id;

  acs_object.new_description(
    v_object_id => :new.object_id,
    v_shortname	=> substr(v_new_one_line_description,0,199),
    v_overview  => :new.overview,
    v_presentation => 'presentation=private,object_type_id=' || :new.object_type_id,
    v_overview_html_p => :new.overview_html_p,
    v_package_id => :new.context_id,
    v_publish_date => v_creation_date);

  -- Fill the content areas
  insert into acs_object_areas
    (object_id, area_id, package_id)
    values
    (:new.object_id, :new.object_type_id, :new.context_id);
end sn_objects_in_tr;
/
show errors

create or replace trigger sn_objects_up_tr
after update of one_line_description, overview, overview_html_p, context_id, public_p, 
  archived_p, in_review_p, expiration_date, user_checkoff_date, original_author_id  on sn_objects
for each row
declare
  v_new_one_line_description sn_objects.one_line_description%TYPE;
  v_presentation acs_objects_description.presentation%TYPE;
  v_state acs_objects_description.state%TYPE;
  v_creation_date acs_objects.creation_date%TYPE;
begin
  if :new.one_line_description is null or length(trim(:new.one_line_description)) = 0 then
    select 'Unnamed ' || pretty_name
    into v_new_one_line_description
    from sn_object_types
    where object_type_id = :new.object_type_id;
  else 
    v_new_one_line_description := :new.one_line_description;
  end if;

  if :new.original_author_id <> :old.original_author_id then
     update acs_objects set creation_user = :new.original_author_id
     where object_id = :new.object_id;
  end if;

  acs_object.set_shortname(
    v_object_id => :new.object_id,
    v_shortname => substr(v_new_one_line_description,0,199));

  acs_object.set_overview(
    v_object_id => :new.object_id,
    v_overview  => :new.overview,
    v_overview_html_p => :new.overview_html_p);

  if :new.context_id <> :old.context_id then
    acs_object.set_package_id(
      v_object_id => :new.object_id,
      v_package_id => :new.context_id);

    update acs_object_areas
    set area_id = :new.object_type_id,
      package_id= :new.context_id
    where object_id = :new.object_id;
  end if;

  if :new.public_p <> :old.public_p
    or :new.archived_p <> :old.archived_p
    or :new.in_review_p <> :old.in_review_p then

    if :new.public_p = 't' then
      v_presentation := 'presentation=public';
    elsif :new.public_p = 'f' then
      v_presentation := 'presentation=private';
    end if;

    v_presentation := v_presentation || ',object_type_id=' || :new.object_type_id;

    if :new.archived_p = 't' then
      v_presentation := v_presentation || ',archived';
    end if;

    if :new.in_review_p = 't' then
      v_presentation := v_presentation || ',in_review';
    end if;

    acs_object.set_presentation(
      v_object_id => :new.object_id,
      v_presentation => v_presentation);
  end if;

  if :new.expiration_date <> :old.expiration_date
    or :new.archived_p <> :old.archived_p then
    if :new.expiration_date <= sysdate then
      v_state := 'd';
    else
      if :new.archived_p = 't' then
        v_state := 'a';
      elsif :new.archived_p = 'f' then
        v_state := 'l';
      end if;
    end if;

    acs_object.set_state(
      v_object_id => :new.object_id,
      v_state => v_state);
  end if;

  if :new.user_checkoff_date <> :old.user_checkoff_date
    or (:new.user_checkoff_date is not null and :old.user_checkoff_date is null) then
    acs_object.set_publish_date(
      v_object_id => :new.object_id,
      v_publish_date => :new.user_checkoff_date);
  end if;

  if :new.user_checkoff_date is null and :old.user_checkoff_date is not null then
    select creation_date
    into v_creation_date
    from acs_objects
    where object_id = :new.object_id;

    acs_object.set_publish_date(
      v_object_id => :new.object_id,
      v_publish_date => v_creation_date);
  end if;

end sn_objects_up_tr;
/
show errors