I would change Count(*) for "If Exists", since that count(*) brings a big
overhead to server once it have to walks for all rows of the table.

Then, your procedure would stay:

CREATE TRIGGER NEW_EXPFUND_CHECK FOR EXPFUND
ACTIVE BEFORE INSERT POSITION 1
AS
BEGIN
/* Check that DECRIPTION has not already been used in a EXPGRP
record. */
IF (EXISTS (SELECT 1 FROM FROM EXPGRP
WHERE UPPER(DESCRIPTION) = UPPER(NEW.DESCRIPTION)) THEN
EXCEPTION DUPLICATE_VALUE;
END
^

It's a good idea have a column called UP_DESCRIPTION maintened by trigers to
save the uppercase value of DESCRIPTION. Create a index on this Column
UP_DESCRIPTION and this speed up the select.
On Before Insert and Before Update Trigger, you can use:
New.UP_DESCRIPTION=UPPER(DESCRIPTION)
to maintain UP_DESCRIPTION updated.

Thenm modify the SP to:

IF (EXISTS (SELECT 1 FROM FROM EXPGRP
WHERE UP_DESCRIPTION = NEW.UP_DESCRIPTION)) THEN

Luiz.

----- Original Message -----
From: Doug Chamberlin <dchamberlin@...>
To: <ib-support@yahoogroups.com>
Sent: Tuesday, February 27, 2001 12:20 PM
Subject: Re: [ib-support] Referential integrity with triggers


> At 2/27/2001 09:30 AM (Tuesday), fabrice.aeschbacher@... wrote:
> >Question: how can a (before insert) trigger prevent from inserting an
> >ORDER if the corresponding CUSTOMER_ID does not exist?
> >Or: how can a (before delete) trigger prevent from deleting a
> >CUSTOMER as long as ORDERs still exist?
>
> Here is an example of the BEFORE INSERT trigger which checks for an
> existing record with the same EXPFUND.DESCRIPTION field value and denies
> the insert if one exists.
>
> First you create an exception.
>
> CREATE EXCEPTION DUPLICATE_VALUE "This value has already been used.";
>
> Then you create the trigger which raises the exception if the constraint
is
> violated. This causes the transaction to fail and the client to receive an
> error.
>
> CREATE TRIGGER NEW_EXPFUND_CHECK FOR EXPFUND
> ACTIVE BEFORE INSERT POSITION 1
> AS
> DECLARE VARIABLE RECORD_COUNT INTEGER;
> BEGIN
> /* Check that DECRIPTION has not already been used in a EXPGRP
record. */
> SELECT COUNT(*) FROM EXPGRP
> WHERE UPPER(DESCRIPTION) = UPPER(NEW.DESCRIPTION)
> INTO :RECORD_COUNT;
> IF (RECORD_COUNT > 0) THEN
> BEGIN
> EXCEPTION DUPLICATE_VALUE;
> END
> END
> ^
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>