Re: HELP: Attempting to use Before Triggers and Sequences to auto-generate Primary Keys

Martin Rose <martin_rose_at_innet.be> wrote in article <01bc79c8$848d3cc0$d8e507c2_at_martin-rose>...
> It is not advisable to change column values in triggers, as ORACLE*Forms
> will say 'Record changed by another user' once the trigger is complete.
>
> Assign your sequences from Forms.
>
>
>
> Douglas Dunnigan <douglas_at_yogananda-srf.org> wrote in article
> <01bc7855$b04fbbe0$63080c26_at_systems-douglas>...
> > I used your code on a V7.3.3 database.
> > It works if I replace
> >
> > :new.Id := Test_SEQ.NEXTVAL;
> >
> > with
> >
> > select Test_SEQ.NEXTVAL into :new.Id from dual;
> >
> >
> > Doug
> >
> >
> > Lee Doty <iarld_at_connectnet.com> wrote in article
> > <33a1c8d4.17499412_at_news.connectnet.com>...
> >
> > > anyone have any idea if this is possible? The functionality I want
 is:
> > > CREATE OR REPLACE TRIGGER Test_TRG
> > > BEFORE INSERT
> > > ON Activator
> > > FOR EACH ROW
> > > WHEN ( new.Id IS NULL )
> > > BEGIN
> > >
> > > :new.Id := Test_SEQ.NEXTVAL;
> > >
> > > END;
> >
> >
>

Actually, its no bad thing to do it in *both* places. The above trigger would fire only if the inital transaction did not include a sequence; useful for data migration or inserting records straight from SQL, ODBC etc.  Belt and Braces, you might call it.

-- 
Miles Thomas
Logica UK Ltd
thomasm "at" logica "dot" com
The above are personal opinions, and are
not necessarily the opinions of my employer.