/*****************************************************************************
*
* Filename         - $Workfile:   flight_triggers.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Archive schema triggers for flight table.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/


create or replace trigger tu_flight
before update on flight
for each row
begin
 
 	/* table cannot be modified (except for archive_flag) */
	if (:new.FLIGHT_ID != :old.FLIGHT_ID) then
		no_archive_modify;
	elsif (:new.PROCESS_NAME != :old.PROCESS_NAME) then
		no_archive_modify;
	elsif (:new.UNIT_NAME != :old.UNIT_NAME) then
		no_archive_modify;
	elsif (:new.FLIGHT_NO != :old.FLIGHT_NO) then
		no_archive_modify;
	elsif (:new.FLIGHT_DATE != :old.FLIGHT_DATE) then
		no_archive_modify;
	elsif (:new.FLIGHT_SCH_TIME != :old.FLIGHT_SCH_TIME) then
		no_archive_modify;
	elsif (:new.FLIGHT_EST_DATE != :old.FLIGHT_EST_DATE) then
		no_archive_modify;
	elsif (:new.FLIGHT_STATUS != :old.FLIGHT_STATUS) then
		no_archive_modify;
	elsif (:new.FLIGHT_STRATEGY != :old.FLIGHT_STRATEGY) then
		no_archive_modify;
	elsif (:new.DEPARTURE_TIME != :old.DEPARTURE_TIME) then
		no_archive_modify;
	elsif (:new.SUBUNIT_NAME != :old.SUBUNIT_NAME) then
		no_archive_modify;
        end if;

end;
/

create or replace trigger td_flight
before delete on flight
for each row
begin

	/*  delete all children in "flight_dests"  */
	delete from
		flight_dests
	where
		flight_id = :old.flight_id;
 
	/*  delete all children in "flight_trace"  */
	delete from
		flight_trace
	where
		flight_id = :old.flight_id;
 
	/*  delete all children in "bag_trace"  */
	delete from
		bag_trace
	where
		flight_id_scan = :old.flight_id;

	/*  delete all children in "pax"  */
	if :old.flight_type = 'I' then
			delete from
			inbound_pax
		where
			inbound_flight_id = :old.flight_id;
	else
		delete from
			pax
		where
			flight_id = :old.flight_id;
	end if;
 
	/*  delete all children in "ulds"  */
	if :old.flight_type = 'I' then
			delete from
			inbound_uld
		where
			inbound_flight_id = :old.flight_id;
	else
		delete from
			ulds
		where
			flight_id = :old.flight_id;
	end if;

	/*  delete all children in "flight_status"  */
	delete from
		flight_status
	where
		flight_id = :old.flight_id;

	/*  delete all children in "flight_access"  */
	delete from
		flight_access
	where
		flight_id = :old.flight_id;

        /* delete all children in handler trace */

        delete from
                handler_trace
        where
                flight_id = :old.flight_id;

        /* delete all children in tracking_log */

        delete from
                tracking_log
        where
                flight_id = :old.flight_id;

       /* delete all children in "hbmd"  */
      delete from 
           hbmd
      where
           flight_id = :old.flight_id;

      /* delete all children in uld trace */
      delete from
          uld_trace
      where
          flight_id = :old.flight_id;

end;
/

