I'm using Oracle as my database, and Dreamweaver to create the input forms.
I need to be able to pass a value that is created automaticlly by the
database via the use of a trigger. Everything works fine when the first user
inputs his/her data, but then I get an error message after that.

Here is the trigger:
create or replace trigger wine_cust_trigger
before insert on wines
for each ro
begin
select customer_id into :new.customer_id from customer ;
end ;
/

Here is the error message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01422: exact fetch returns more than requested number
of rows ORA-06512: at "LTRAUB.WINE_CUST_TRIGGER", line 2 ORA-04088: error
during execution of trigger 'LTRAUB.WINE_CUST_TRIGGER'
/phoenix/wines.asp, line 115

Any help would be appreciated.

Thanks.

use an Oracle "sequence" to provide the number for the new customer_id,
e.g.:

create sequence seq_cust_id start with 1 increment by 1 nocycle
nomaxvalue cache 20;

select seq_cust_id.nextval into :new.customer_id from dual;

that should get you started.

-bdbafh

you are selecting ALL customer_ids into :new.customer_id...

not complete syntax
create sequence newcustseq.....

create or replace trigger wine_cust_trigger
before insert on wines
for each ro
begin
select newcustseq.nextval into :new.customer_id from dual;
end ;
/

OR

this very poor-man's sequence that is not very scalable due to locking
etc..
create or replace trigger wine_cust_trigger
before insert on wines
for each ro
begin
select max(customer_id)+1 into :new.customer_id from customer ;
end ;
/

Thanks that did the trick.

I forgot about the max function. Why the +1? I tried it with out that and it
works. My next question is what would happen if this application is used
accross multiple computers at the same time? Would the correct customer_id
by identified with the correct customer?

"onedbguru" <onedbguru@firstdbasource.com> wrote in message
news:1145390327.156831.163740@v46g2000cwv.googlegr oups.com...
> you are selecting ALL customer_ids into :new.customer_id...
>
> not complete syntax
> create sequence newcustseq.....
>
> create or replace trigger wine_cust_trigger
> before insert on wines
> for each ro
> begin
> select newcustseq.nextval into :new.customer_id from dual;
> end ;
> /
>
> OR
>
> this very poor-man's sequence that is not very scalable due to locking
> etc..
> create or replace trigger wine_cust_trigger
> before insert on wines
> for each ro
> begin
> select max(customer_id)+1 into :new.customer_id from customer ;
> end ;
> /
>

Comments embedded.
TekDragon wrote:
> Thanks that did the trick.
>
> I forgot about the max function. Why the +1? I tried it with out that and it
> works.

Because you don't have customer_id declared as a primary key, so you
can have duplicate entries in your table. This is what your current
trigger produces.

> My next question is what would happen if this application is used
> accross multiple computers at the same time? Would the correct customer_id
> by identified with the correct customer?
>

No, and with the code you're implementing a single user won't get a
correctly identified customer, either. The max(col) + 1 increments the
current highest value in the col and (hopefully) prevents duplicates.
Of course, it won't once you have more than one user connected. Any
select against the target table will produce duplicates between
sessions as the 'select' can only 'see' what has been committed, not
what is in process. A sequence has no such issues.

> "onedbguru" <onedbguru@firstdbasource.com> wrote in message
> news:1145390327.156831.163740@v46g2000cwv.googlegr oups.com...
> > you are selecting ALL customer_ids into :new.customer_id...
> >
> > not complete syntax
> > create sequence newcustseq.....
> >
> > create or replace trigger wine_cust_trigger
> > before insert on wines
> > for each ro
> > begin
> > select newcustseq.nextval into :new.customer_id from dual;
> > end ;
> > /
> >
> > OR
> >
> > this very poor-man's sequence that is not very scalable due to locking
> > etc..
> > create or replace trigger wine_cust_trigger
> > before insert on wines
> > for each ro
> > begin
> > select max(customer_id)+1 into :new.customer_id from customer ;
> > end ;
> > /
> >

I'd be using a sequence and a trigger to ensure you have unique entries
across sessions. I would also make your customer_id your primary key,
so it's both unique and not null; this, of course, would cause your
current trigger to fail on insert, and it may cause the creation of the
constraint to generate errors as you have multiple records with the
exact same customer_id (this is the problem your current trigger
creates).

You asked for assistance; it's foolish of you to not take that which is
offered.


David Fitzjarrell