/*****************************************************************************
*
* Filename         - $Workfile:   code_share_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         - Live schema - triggers for code_share table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   code_share_triggers.sql  $  $Revision:   1.1  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/triggers/code_share_triggers.svl  $
* 
*    Rev 1.1   21 Jun 2001 15:04:02   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
*    Rev 1.0   03 Jun 1998 12:15:02   tagtrak
* Initial check-in to PVCS
*
****************************************************************************/

create or replace trigger ti_code_share
before insert on code_share
for each row

declare
	found number;

begin

	/*
	 * Check for duplicates.
	 */
	select
		count(*)
	into
		found
	from
		code_share
	where
		direction = :new.direction and
		op_carrier = :new.op_carrier and
		op_fltno  = :new.op_fltno and
		trunc(op_date)  = trunc(:new.op_date) and
		cs_carrier = :new.cs_carrier and
		cs_fltno  = :new.cs_fltno and
		trunc(cs_date)  = trunc(:new.cs_date) and
    airport_code    = :new.airport_code;

	if ( found > 0 ) then
		raise_application_error(-20037,'duplicate code_share record - rejected');
	end if;

	/*
	 * Remove any old code-share records for this flight.
	 */
	delete from
		code_share
	where
		cs_carrier = :new.cs_carrier and
		cs_fltno = :new.cs_fltno and
		to_char(cs_date,'DDMON') = to_char(:new.cs_date,'DDMON') and
    airport_code = :new.airport_code;
		
	delete from
		code_share
	where
		op_carrier = :new.cs_carrier and
		op_fltno = :new.cs_fltno and
		to_char(op_date,'DDMON') = to_char(:new.cs_date,'DDMON') and
    airport_code = :new.airport_code;

	delete from
		code_share
	where
		cs_carrier = :new.op_carrier and
		cs_fltno = :new.op_fltno and
		to_char(cs_date,'DDMON') = to_char(:new.op_date,'DDMON') and
    airport_code = :new.airport_code;
		
	:new.timestamp := SYSDATE;
		
end;
/
create or replace trigger tia_code_share
after insert on code_share
for each row
declare 
  psiteid   site_info.site_id%type;
  retcode   number(10); 
begin

  psiteid := multi_site.get_site_id(:new.airport_code);

	if (:new.direction = 'O' ) then
		codeshare.maintain_codeshare(
			:new.op_carrier,
			:new.op_fltno,
			:new.op_date,
      psiteid,
			:new.cs_carrier,
			:new.cs_fltno,
			:new.cs_date
		);
	end if;

end;
/

create or replace trigger td_code_share
after delete on code_share
for each row
declare
	fltno char(8);
  psiteid   site_info.site_id%type;
  retcode   number(10);
begin
  
  psiteid := multi_site.get_site_id(:new.airport_code);
	codeshare.compose_flight_no(:old.cs_carrier, :old.cs_fltno, fltno);

	update
		flight
	set
		op_flight_id = NULL
	where
		flight_no   = fltno and
		flight_date = to_char( :old.cs_date, 'DDMON' ) and
    site_id     = psiteid                          and
		op_flight_id is not null;

end;
/
