On Mar 10, 5:07 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Yes sir you are correct. This work was for an Oracle class. The goal
was to look at an alternate way of enforcing RI with a trigger which,
unfortunately, is a common practice. I did figure it out by the way.
Received on Sun Apr 15 2007 - 21:03:15 CDT
