On Mar 10, 3:01 pm, "rm" <gro..._at_rlmoore.net> wrote:
> Before inserting a row in a table I would like to check the value of a
> particular field in the new candidate row against the existence of the
> value in a field in another table.
>
> How do I "grab" the new value?
>
> Example:
>
> CREATE OR REPLACE TRIGGER check_zip_trig
> BEFORE INSERT
> ON address
> REFERENCING NEW AS New OLD AS Old
> FOR EACH ROW
>
> IF NOT EXISTS(SELECT ZIPCODE FROM ZIP WHERE ZIPCODE = NEW:ZIP;)
> THEN
> RAISE_APPLICATION_ERROR (- 20212, 'Cannot perform insert.
> Invalid ZIP Code');
> END IF;
>
> END ;
This is precisely what foreign keys are designed to do, prevent
unreferenced values from entering a child table. Attempting to do
this with a trigger is an exercise in futility; implement a proper
foreign key constraint instead.
David Fitzjarrell
Received on Sat Mar 10 2007 - 15:07:32 CST
