/*****************************************************************************
*
* Filename              - bag_status_triggers.sql
*
* Project Name          - UltraTrak Database
*
* Source Code Identifier - UAS/URBRS/SS/10127
*
* Description           - Triggers for BAG_STATUS table. These triggers
*                         check the when a row is inserted the given status no
*                         does not already exist in the TRACKING_EVENTS table.
*                         This is required for clarity as the MWS displays 
*                         data from both these tables on the same page
*
* Language              - Oracle PL/SQL
*
* File History          - For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2003
*
*****************************************************************************/

/*
 * Before each insert into the bag_status table
 */

CREATE OR REPLACE TRIGGER ti_bag_status
BEFORE INSERT OR UPDATE ON bag_status
FOR EACH ROW
BEGIN
DECLARE
   l_count integer := 0;
BEGIN

   -- Check the new status number does not exist in the
   -- tracking_events table.

   SELECT count(*) INTO l_count FROM tracking_events WHERE
      event_no = :new.status_no;
      
   IF l_count > 0 THEN

      raise_application_error(-20100,'status/event number already in use');

   END IF;
   
   IF :new.status_no > 9999 THEN
         raise_application_error(-20044,'Status number is too big. Should be 9999 or less');
   END IF;
   
END;
END;
/
