-- The customer number in Prop_Owner is a foreign key referencing the
-- Customer table, optimized
-- Valid-time sequenced/transaction-time current
CREATE OR REPLACE TRIGGER P_O_integrity
AFTER INSERT OR UPDATE OR DELETE  ON Prop_Owner
DECLARE
  valid INTEGER;
BEGIN
 SELECT 1 INTO valid FROM DUAL
 WHERE
  NOT EXISTS 
  (
   SELECT * FROM Prop_Owner P
    WHERE P.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
          NOT EXISTS
          (  SELECT * FROM Customer C
             WHERE P.customer_number = C.customer_number AND
                   C.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
                   C.VT_Begin <= P.VT_Begin AND
                   P.VT_Begin < C.VT_End )
          OR NOT EXISTS
          (  SELECT * FROM Customer C
             WHERE P.customer_number = C.customer_number AND
                   C.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
                   C.VT_Begin < P.VT_End AND
                   P.VT_End <= C.VT_End )
  );
 EXCEPTION
  WHEN NO_DATA_FOUND THEN 
       RAISE_APPLICATION_ERROR( -20001, '' );
END;
/

