-- Validate that property_number is a valid-time sequenced, transaction-time
-- sequenced key on Prop_Owner
CREATE OR REPLACE TRIGGER P_O_seq_primary_key  
AFTER INSERT OR UPDATE 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' )
                  )
  );
 EXCEPTION
  WHEN NO_DATA_FOUND THEN 
       RAISE_APPLICATION_ERROR( -20001, 'primary key violated in Prop_Owner' );
END;
/
