--
-- $Id: photo-album-lite-triggers.sql,v 1.1 2001/04/20 20:51:11 donb Exp $
--

create or replace trigger pl_photos_ins_update_tr
before insert or update on pl_photos
for each row
declare
 v_creation_user integer;
 v_email varchar(100);
 v_sort_n integer;
begin
    select creation_user into v_creation_user from acs_objects where object_id = :new.photo_id;
    select email into v_email from parties where party_id = v_creation_user;

    :new.indexable_stuff := :new.client_filename || ' ' || :new.caption || ' ' || person.name(v_creation_user) || ' ' || v_email;

  if :old.folder_id is null or 
     :new.folder_id != :old.folder_id then
    -- new photo or moved photo
    insert into pl_reshuffle_queue (folder_id)
      values (:new.folder_id);
 
    if :old.folder_id is not null then
    -- moved photo
     insert into pl_reshuffle_queue (folder_id)
      values (:old.folder_id);

     -- Assign a large, unique sort_n to the moved photo.
     -- Call pl_reshuffle within the transaction to bump it back down.
     select pl_moved_photo_sort_n_seq.nextval into v_sort_n from dual;
     :new.sort_n := v_sort_n;

    end if;

  end if;

end pl_photos_ins_update_tr;
/
show errors

create or replace trigger pl_photos_post_update_tr
after update on pl_photos
for each row
begin
  update acs_objects
  set last_modified = sysdate,
      context_id = :new.folder_id                 
  where object_id = :new.photo_id;

  if :new.deleted_p = 't' then
   insert into pl_reshuffle_queue (folder_id)
    values (:new.folder_id);
  end if;
end pl_photos_post_update_tr;
/
show errors

create or replace trigger pl_folders_update_tr
after update on pl_folders
for each row
begin
  update acs_objects set last_modified = sysdate
  where object_id = :new.folder_id;
end pl_folders_update_tr;
/
show errors