/*****************************************************************************
*
* Filename         - $Workfile$
*
* Author           - Michael Clark
* Last Update      - $Date$
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          -
*
* Contents         - Live Schema: triggers on table uld_dests
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

/*
 * Trigger uld_dests_ibr generates the next sequence number for the ULD
 * destination and inserts it into the column dest_order.
 */

CREATE OR REPLACE TRIGGER uld_dests_ibr
BEFORE INSERT ON uld_dests
FOR EACH ROW
BEGIN
   SELECT MAX(dest_order)
   INTO :new.dest_order
   FROM uld_dests
   WHERE uld_no = :new.uld_no
   AND flight_id = :new.flight_id;
   
   IF :new.dest_order IS NULL THEN
      :new.dest_order := 1;
   ELSE
      :new.dest_order := :new.dest_order + 1;
   END IF;
END;
