Re: Ray needs your help on sequences/triggers!

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 9 Mar 2004 08:58:23 -0500
Message-ID: <GqydnT5It5AVUNDdRVn-uQ@comcast.com>

"Ray Godfrey" <cord_u_roy_at_yahoo.com> wrote in message news:1421dc84.0403090443.162ed02e_at_posting.google.com...
| Bricklen <bricklen-rem_at_yahoo.comz> wrote in message
news:<i023c.121055$A12.8483_at_edtnps84>...
| > Ray Godfrey wrote:
| >
| > > i have found an example from this site
| > > http://jen.fluxcapacitor.net/geek/autoincr.html
| > > following their instructions i have applied it toward my own code and
| > > successfully created the sequences
| > >
| > > i have then tried to insert the relevent trigger but it keeps going to
| > > the next line,
| > > e.g
| > > SQL> create trigger STUPROPTRIG before insert on STUPROJPROP for each
| > > row begin select NEXTSTUPROP.n
| > > extval into :new.STUPROP_ID from dual;
| > > 2 end;
| > > 3
| > > 4
| > > i then tried it again and typed commit; hoping it would work,
| > > any suggestions would be appriecated,
| > > thanks,
| > > your buddy, ;-)
| > > Ray.....
| > missing a slash after the "end;"
| > ...
| > end;
| > /

|

| ok thats gr8 thanks, just on emore question..

|

| on the site a guy says that with the following you dont need triggers
| insert into test values(test_seq.nextval, 'voila!');

|

| as i am populating my fields from text boxes in a jsp front end should
| i use that instead of triggers??
| your buddy
| Ray!!

i'd say no -- you really want your database set up so that use the sequence cannot be overridden. so if inserts are being done outside of stored procedures/packages, use a trigger. but if all DML is encapsulated in a PL/SQL table API, then use the sequence within the insert statement to avoid the 'select from dual'

;-{ mcs Received on Tue Mar 09 2004 - 07:58:23 CST