-- Validate that property_number is a valid-time sequenced, transaction-time
-- sequenced key on Prop_Owner
-- Added is an assertion forcing the history of valid time values for each
-- property number to be contiguous
CREATE OR REPLACE TRIGGER P_O_seq_primary_key  
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 P1  
    WHERE property_number IS NULL  
          OR 1 < (SELECT COUNT(customer_number)  
                  FROM Prop_Owner P2  
                  WHERE P1.property_number = P2.property_number  
                        AND P1.VT_Begin < P2.VT_End  
                        AND P2.VT_Begin < P1.VT_End  
                        AND P1.TT_Stop = TO_DATE('12/31/9999','MM/DD/YYYY' )
                        AND P2.TT_Stop = TO_DATE('12/31/9999','MM/DD/YYYY' )
                  )
  )
  AND NOT EXISTS
  ( SELECT * FROM Prop_Owner P, Prop_Owner P2
    WHERE P.VT_End < P2.VT_Begin
          AND P.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' )
          AND P2.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' )
          AND NOT EXISTS
          ( SELECT * FROM Prop_Owner P3
            WHERE P3.property_number = P.property_number
                  AND (((P3.VT_Begin <= P.VT_End) AND 
                        (P.VT_End < P3.VT_END))
                      OR 
                      ((P3.VT_Begin <= P2.VT_Begin) AND
                       (P2.VT_Begin <= P3.VT_End)))
                  AND P3.TT_Stop = TO_DATE( '12/31/9999', 'MM/DD/YYYY' )
          )
  );
 EXCEPTION
  WHEN NO_DATA_FOUND THEN 
       RAISE_APPLICATION_ERROR( -20001, 'primary key violated or failure to maintain sequential valid time history in Prop_Owner' );
END;
/
