Can a Function-Based Index do this?

This follows from a previous thread (see: "Testing for Existence of Rows - What's Fastest?"). A function based index was recommended but I'm only now probing for details.

Basically we want to establish uniqueness on a field value, but the uniqueness doesn't apply to the whole table, just to groups of records. Consider a table called "orders." For records of order_type "A", we want to check that the field po_num is unique for certain records. We have a trigger
(after insert on "orders") that does this; it looks something like this
(pseudocode):

IF :new.order_type = "A" THEN

   SELECT count(*)
   INTO num_recs
   FROM orders
   WHERE order_whse = :new.order_whse
   AND cust_id = :new.cust_id
   AND po_num = :new.po_num;
END IF;
--I may be using ":new" wrong here; I
--don't have the code in front of me

IF num_recs > 1 THEN

   RAISE_APPLICATION_ERROR(...)
END IF; So we want to check that no orders with the new po_num already exist, for orders for a certain customer from a certain warehouse. Note that it should find one record (itself) since this is an after insert trigger. (Doing it before the insert causes a mutating table error, so there's actually some trickery in place to make this work. We use the old trick of writing values to package variables on a before trigger).

I'd like to do away with all this, since two triggers and a package are needed--not to mention that the SELECT COUNT(*) takes a long time on this table. Can a function-based index on the table handle this? If so, how? All I've found on the web talks about case sensitivity and such--nothing like this.

Thanks in advance.

-jk Received on Sun Jun 02 2002 - 19:08:57 CDT