Re: Trigger to Prevent Duplicates

Solution 1: (Oracle 8i and above)
1) Create a function that is an autonomous transaction (to avoid mutating table error). This function will read the table to see if a duplicate exists. Return boolean.
2) Create a BEFORE INSERT/UPDATE trigger that calls this function. If duplicate, then raise an error, will cause the transaction to be rejected.

Simple Unique constraint will not work because some products allow duplication that day.

Another (Less clear) solution would be to create a unique constraint on customer/product/sequence. Increment sequence in the trigger for products that allow duplicates, don't increment for those that don't.

>
Received on Mon Mar 18 2002 - 16:23:02 CST