oracle 9i, error with trigger

[thyduong]

when I do

INSERT into ODetail VALUES (corseq.currval,24,10,4.1)

from a java program, i got an error. Pls see below error message, trigger, and table ddl

//start error
Exception in thread "main" java.sql.SQLException: ORA-02290: check constraint (S
YSTEM.SYS_C003789) violated
ORA-06512: at "SYSTEM.INSERT_ODETAIL", line 17
ORA-04088: error during execution of trigger 'SYSTEM.INSERT_ODETAIL'

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:109
3)
at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.ja
va:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.jav
a:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStateme
nt.java:2709)
at oracle.jdbc.driver.OracleStatement.executeUpdate(O racleStatement.java
:796)
at DBUtil.update(DBUtil.java:78)
at SDD.insertODetail(SDD.java:370)
at SDD.proceedToCheckOut(SDD.java:333)
at SDD.placeOrd(SDD.java:313)
at SDD.main(SDD.java:40)
Press any key to continue . . .
//end error

DROP TABLE Supplier cascade constraints;
DROP TABLE Item cascade constraints;
DROP TABLE Customer cascade constraints;
DROP TABLE COrder cascade constraints;
DROP TABLE ODetail cascade constraints;
DROP TABLE Supplies cascade constraints;
DROP TABLE Restock cascade constraints;

CREATE TABLE Supplier (
s# integer primary key,
sname varchar(25),
phone varchar(15),
address varchar(70)
);

CREATE TABLE Item (
i# integer primary key,
iname varchar(23),
qoh integer check (qoh > 0),
rop integer check (rop > 0 ),
roq integer check (roq > 0),
mqoh integer check (mqoh > 0),
up number check (up > 0)
);

CREATE TABLE Customer(c# integer primary key,
cname varchar(25),
category integer check (category > 0 ),
cphone varchar(15),
caddress varchar(70)
);

CREATE TABLE COrder (
o# integer primary key,
drc# integer references Customer,
date_ordered date,
date_ship date
);

CREATE TABLE ODetail (
o# integer references COrder,
i# integer references Item,
quantity integer check (quantity > 0),
sup number check ( sup > 0 ),
primary key (o#,i#)
);


CREATE TABLE Supplies (
s# integer references Supplier,
i# integer references Item,
primary key (s#,i#)
);

CREATE TABLE Restock(
rsk# integer,
odate date,
i# integer references Item,
rtype char check (rtype in('Y', 'N')),
primary key(rsk#)
);

DROP SEQUENCE supseq;
DROP SEQUENCE itemseq;
DROP SEQUENCE cusseq;
DROP SEQUENCE corseq;
DROP SEQUENCE odeseq;
DROP SEQUENCE rskseq;

CREATE SEQUENCE supseq start with 1;
CREATE SEQUENCE itemseq start with 1;
CREATE SEQUENCE cusseq start with 1;
CREATE SEQUENCE corseq start with 1;
CREATE SEQUENCE odeseq start with 1;
CREATE SEQUENCE rskseq start with 1;

CREATE or replace TRIGGER insert_odetail
before insert on ODetail
for each row
declare
my_qoh Item.qoh%type;
my_rop Item.rop%type;
begin
select qoh, rop
into my_qoh, my_rop
from Item
where item.i# = :new.i#;
if(:new.quantity <= my_qoh) then
update item
set qoh = qoh - :new.quantity
where item.i# = :new.i#;
if((my_qoh - :new.quantity) < my_rop) then
update item
set qoh = qoh + roq
where item.i# = :new.i#;
insert into restock values (rskseq.nextval, sysdate, :new.i#,'n');
end if;
end if;
end;
/

[jkoopmann]

looks like you are creating these objects and trigger under the system account.
you really should think about creating an application user.
also, you should name your constraints within the ddl and then the error will point you to the proper table and constraint that it is complaining about.

alternativly you can query the dba_constraints view where constraint_name = 'SYS_C003789' and it will point you to the complaining table and hopefully your issue.

[thyduong]

i got it to work. thank you so much.