-- ASSERT that no customers who own property 7797 shall own another property any
-- other properties nontemporally
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 P2.customer_number
           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 
  );
 EXCEPTION
  WHEN NO_DATA_FOUND THEN 
       RAISE_APPLICATION_ERROR( -20001, 'NOPE! the owner of 7797 can own no other property' );
END;
/
