/*****************************************************************************
*
* Filename              - tracking_events_triggers.sql
*
* Project Name          - UltraTrak Database
*
* Source Code Identifier - UAS/URBRS/SS/10127
*
* Description           - Triggers for TRACKING_EVENTS table. These triggers
*                         check the when a row is inserted the given status no
*                         does not already exist in the BAG_STATUS 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 tracking_events table
 */

CREATE OR REPLACE TRIGGER ti_tracking_events
BEFORE INSERT ON tracking_events
FOR EACH ROW
BEGIN
DECLARE
   l_count integer := 0;
BEGIN

   -- Check that the new link event exists

   IF :new.link_event IS NOT NULL THEN
      SELECT COUNT (*) INTO l_count FROM tracking_events WHERE event_no = :new.link_event;
      IF l_count = 0 THEN
         -- The link event doesn't exist - raise an error
         raise_application_error (-20000, 'link event does not exist');
      END IF;
   END IF;

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

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

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

   END IF;

   IF :new.link_event = :new.event_no THEN

      raise_application_error(-20101,'recursive events not allowed');

   END IF;

   IF :new.event_no > 9999 THEN
      raise_application_error(-20045,'Event number is too big. Should be 9999 or less');
   END IF;

END;
END;
/

CREATE OR REPLACE TRIGGER tu_tracking_events
BEFORE UPDATE ON tracking_events
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 bag_status WHERE
      status_no = :new.event_no;
      
   IF l_count > 0 THEN

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

   END IF;

   IF :new.link_event = :new.event_no THEN

      raise_application_error(-20101,'recursive events not allowed');

   END IF;

   IF :new.event_no > 9999 THEN
      raise_application_error(-20045,'Event number is too big. Should be 9999 or less');
   END IF;

END;
END;
/
