On Apr 15, 10:03 pm, "rm" <gro..._at_rlmoore.net> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
I would ask my money back if I went to a class teaching me how to do
the things the wrong way. There are enough places where that became a
practice, so you can get payed for doing so instead of you paying for
it - my two cents.
Received on Tue Apr 17 2007 - 11:28:03 CDT
