-- Assert that the customer number in Prop_Owner is a foreign keyh referencing the
-- Customer table
-- 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 I1.customer_number FROM Prop_Owner I1, Customer I3
    WHERE I1.customer_number = I3.customer_number AND
          I1.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
          I3.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
          NOT EXISTS( SELECT * FROM Customer I4
                      WHERE I4.customer_number = I1.customer_number AND
                            I4.TT_Stop = TO_DATE( '12/31/9999',
'MM/DD/YYYY' ) AND
                            I1.VT_Begin < I4.VT_End AND
                            I4.VT_Begin < I3.VT_Begin )
   UNION
     SELECT I1.customer_number FROM Prop_Owner I1, Customer I2
     WHERE I1.customer_number = I2.customer_number AND
           I1.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
           I2.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
           NOT EXISTS( SELECT * FROM Customer I4
                       WHERE I4.customer_number = I1.customer_number AND
                             I4.TT_Stop = TO_DATE( '12/31/9999',
'MM/DD/YYYY' ) AND
                             I2.VT_End < I4.VT_End AND
                             I4.VT_Begin < I1.VT_End )
   UNION
     SELECT I1.customer_number FROM Prop_Owner I1, Customer I2, Customer
I3
     WHERE I1.customer_number = I2.customer_number AND
           I1.customer_number = I3.customer_number AND
           I1.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
           I2.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
           I3.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
           NOT EXISTS( SELECT * FROM Customer I4
                       WHERE I4.customer_number = I1.customer_number AND
                             I4.TT_Stop = TO_DATE( '12/31/9999',
'MM/DD/YYYY' ) AND
                             I2.VT_End < I4.VT_End AND
                             I4.VT_Begin < I3.VT_Begin )
   UNION
     SELECT I1.customer_number FROM Prop_Owner I1
     WHERE I1.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' ) AND
           NOT EXISTS( SELECT * FROM Prop_Owner I4
                       WHERE I4.customer_number = I1.customer_number AND
                             I4.TT_Stop = TO_DATE( '12/31/9999',
'MM/DD/YYYY' ) AND
                             I1.VT_Begin < I4.VT_End AND
                             I4.VT_Begin < I1.VT_End )

  ) ;

 EXCEPTION
  WHEN NO_DATA_FOUND THEN 
       RAISE_APPLICATION_ERROR( -20001, '' );
END;
/
