Testing for Existence of Rows - What's Fastest?

I've discovered a trigger in our database that is a real resource hog. The trigger happens AFTER INSERT on tableA and it looks something like this...

SELECT count(colA)
INTO varCount
FROM tableA -- has ~300,000 rows
WHERE... ; IF varCount > 1 THEN

   RAISE_APPLICATION_ERROR... ;
END IF; All it's doing is insuring that no record exists with the same particular property of the one just inserted (hence > 1). This is all fine, but there's got to be a better way to write the query. COUNT(*) gives us more information that we need, and we take a performance hit because of it. My thought was something like this...

BEGIN
   SELECT colA
   INTO varA
   FROM tableA
   WHERE... ;
EXCEPTION WHEN TOO_MANY_ROWS THEN
   RAISE_APPLICATION_ERROR... ;
EXCEPTION WHEN OTHERS THEN
   NULL;
END; But this is a trigger, so if there's a more optimal way yet, I'd be curious to see it. Thanks again...

-jk Received on Sat May 25 2002 - 12:45:32 CDT