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
^