-- ASSERT that no customer may own property 7797 concurrently with any other
-- property
-- Valid-time sequenced and 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 P1, Prop_Owner P2
    WHERE P1.property_number = 7797
          AND P2.property_number <> P1.property_number
          AND P1.customer_number = P2.customer_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, 'NOPE! nobody can own 7797 and any other property at the same time' );
END;
/
