/*****************************************************************************/
/*                                                                           */
/*  File name :   wfpc2_reffile.trig                                         */
/*                                                                           */
/*  Description : wfpc2 insert, update, and delete triggers for the reffile  */
/*                  database                                                 */
/*                                                                           */
/*  Modification History:                                                    */
/*                                                                           */
/*    Date       OPR      Who                       Reason                   */
/*  --------   -------    ---   -------------------------------------------  */
/*  04/28/10   65026       mss  first version, from wfpc2.trig               */
/*                                                                           */
/*****************************************************************************/


/*****************************************************************************/
/* wfpc2_file_insert                                                         */
/*****************************************************************************/

drop trigger wfpc2_file_insert
go
create trigger wfpc2_file_insert
on wfpc2_file 
for insert as
begin
  declare @rel_info_type char(1)      /* relation_info.type, I(image) or T(table) */

  if (select count(*) from inserted) > 1
    begin  /* too many inserts */
      /* display error message */
      print '*******************************************************************'
      print 'CDBS Error: Attempting to insert more than 1 record into wfpc2_file '
      select delivery_number,reference_file_type,file_name,expansion_number,
        useafter_date,opus_flag,opus_load_date,archive_date, otfc_date,
        general_availability_date,reject_flag,reject_delivery_number,
        reject_by_file_name,reject_by_expansion_number from inserted
      print '*******************************************************************'
      print ' '

      rollback transaction
      print 'NO RECORDS WERE INSERTED'
      print ' '
      return
    end /* too many inserts */


  select @rel_info_type = r.type 
    from relation_info r, inserted i
   where r.reference_file_type = i.reference_file_type
     and r.instrument = 'WFPC2'

  /* Table record */
  if @rel_info_type = 'T'
    begin /* Table record */

      update wfpc2_file
         set wfpc2_file.reject_flag = 'Y',
             wfpc2_file.reject_delivery_number = inserted.delivery_number,
             wfpc2_file.reject_by_file_name = inserted.file_name,
             wfpc2_file.reject_by_expansion_number = inserted.expansion_number
        from wfpc2_file, inserted
       where wfpc2_file.reference_file_type = inserted.reference_file_type
         and wfpc2_file.useafter_date = inserted.useafter_date
         and wfpc2_file.reject_flag = 'N'
         and wfpc2_file.opus_flag = inserted.opus_flag
         and wfpc2_file.file_name != inserted.file_name 
             /* can never reject a file with same file name */
    end  /* Table record */


  /* Image record */
  if @rel_info_type = 'I'
    begin /* Image record */

      /* check existence of corresponding row level record */
      if not exists (select *
            from wfpc2_row, inserted
           where wfpc2_row.file_name = inserted.file_name
             and wfpc2_row.expansion_number = inserted.expansion_number) 
        begin  /* row level record not found */
         
          /* display error message */
          print '*******************************************************************'
          print 'CDBS Error: No row level record for wfpc2_file'
          select file_name, expansion_number from inserted
          print '*******************************************************************'
          print ' '

          /* row_level record not found, reject inserted file_level record */
          rollback transaction
          print 'NO RECORDS WERE INSERTED'
          print ' '
          return
        end /* row level record not found */


      /* set reject record */
      update wfpc2_file
         set wfpc2_file.reject_flag = 'Y',
             wfpc2_file.reject_delivery_number = i.delivery_number,
             wfpc2_file.reject_by_file_name = i.file_name,
             wfpc2_file.reject_by_expansion_number = i.expansion_number
        from wfpc2_file n, inserted i, wfpc2_row r1, wfpc2_row r2
       where n.reference_file_type = i.reference_file_type
         and n.useafter_date = i.useafter_date
         and n.reject_flag = 'N'
         and n.opus_flag = i.opus_flag
         and n.file_name != i.file_name
         and r1.file_name = i.file_name
         and r1.expansion_number = i.expansion_number
         and r1.atodgain = r2.atodgain
         and r1.mode = r2.mode
         and r1.serials = r2.serials
         and r1.shutter = r2.shutter
         and ( ( r1.filter1 = r2.filter1
             and r1.filter2 = r2.filter2)
           or
               ( r1.filter1 = r2.filter2
             and r1.filter2 = r2.filter1) )
         and n.file_name = r2.file_name
         and n.expansion_number = r2.expansion_number
    end /* Image record */  

end  /* for insert */
go

/*****************************************************************************/
/* wfpc2_file_update                                                         */
/*****************************************************************************/

drop trigger wfpc2_file_update
go
create trigger wfpc2_file_update
on wfpc2_file
for update as
begin

  /* check for too many inserts */
  if exists (select * from inserted, deleted
      where inserted.opus_flag = 'Y' and inserted.reject_flag = 'Y'
        and deleted.reject_flag = 'N' and inserted.file_name = deleted.file_name
        and inserted.expansion_number = deleted.expansion_number) 
    begin /* if select count */
  
      if (select count(*) from inserted) > 1
        begin  /* too many inserts */
          /* display error message */
          print '*******************************************************************'
          print 'CDBS Error: Attempting to multi-reject in wfpc2_file'
          select delivery_number,reference_file_type,file_name,expansion_number,
           useafter_date,opus_flag,opus_load_date,archive_date, otfc_date,
           general_availability_date,reject_flag,reject_delivery_number,
           reject_by_file_name,reject_by_expansion_number from inserted
          print '*******************************************************************'
          print ' '

          rollback transaction
          print 'NO RECORDS WERE INSERTED'
          print ' '
          return
        end /* too many inserts */

    end  /* if select count  */

end  /* update */

go


/*****************************************************************************/
/* wfpc2_file_delete                                                         */
/*****************************************************************************/

drop trigger wfpc2_file_delete
go
create trigger wfpc2_file_delete
on wfpc2_file
for delete as
begin

  /* reject delete if general_availability_date is not null */
  if exists (select * from deleted
     where general_availability_date is not NULL) 
    begin /* wfpc2_file.general_availability_date is not null */

      /* display error message */
      print '*******************************************************************'
      print 'CDBS Error: Attempting to delete wfpc2_file record(s) for delivered data'
      select delivery_number,reference_file_type,file_name,expansion_number,
        useafter_date,opus_flag,opus_load_date,archive_date,otfc_date,
        general_availability_date,reject_flag,reject_delivery_number,
        reject_by_file_name,reject_by_expansion_number
      from deleted where general_availability_date is not NULL
      print '*******************************************************************'
      print ' '

      /* rollback deleted wfpc2_file data */
      rollback transaction
      print 'NO RECORDS WERE DELETED'
      print ' '
      return
    end  /* wfpc2_file.general_availability_date is not null */
end  /* delete file level record */
go

/*****************************************************************************/
/* wfpc2_row_delete                                                          */
/*****************************************************************************/

drop trigger wfpc2_row_delete
go
create trigger wfpc2_row_delete
on wfpc2_row
for delete as
begin /* for delete row level data */

  /* check corresponding file level table's general_availability_date */
  /* reject delete if general_availability_date is not null           */
  if exists (select * from wfpc2_file w, deleted d
       where w.file_name = d.file_name and w.expansion_number = d.expansion_number 
         and general_availability_date is not NULL) 
    begin /* wfpc2_file.general_availability_date is not null */

      /* display error message */
      print '*******************************************************************'
      print 'CDBS Error: Attempting to delete wfpc2_row record(s) for delivered data'
      select w.delivery_number,w.reference_file_type,w.file_name,
             w.expansion_number,w.useafter_date,w.opus_flag,w.opus_load_date,
             w.archive_date,w.general_availability_date,w.otfc_date,w.reject_flag,
             w.reject_delivery_number,w.reject_by_file_name,
             w.reject_by_expansion_number
        from wfpc2_file w, deleted d
       where w.file_name = d.file_name
         and w.expansion_number = d.expansion_number
         and w.general_availability_date is not NULL
      print '*******************************************************************'
      print ' '

      /* rollback deleted wfpc2_row data */
      rollback transaction
      print 'NO RECORDS WERE DELETED'
      print ' '
      return
    end  /* wfpc2_file.general_availability_date is not null */
end  /* for delete row level data */
go