oracle 9i, create trigger

[thyduong]

i got "trigger created with compilation error."
pls see below my trigger and tables.


//start of trigger
CREATE TRIGGER insert_odetail
before insert on ODetail
declare my_qoh Item.qoh%type;
my_rop Item.rop%type;
for each row
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 (sysdate, :new.i#);
end if;
end if;
end;
//end of trigger

//schema
CREATE TABLE Item (
i# integer primary key,
iname varchar(23),
qoh integer check (qoh > 0), //quantity on hand
rop integer check (rop > 0 ), //reorder point
roq integer check (roq > 0), //reorder quantity
mqoh integer check (mqoh > 0), //max quantity on hand
up integer check (up > 0) //unit price
);

CREATE TABLE ODetail ( //order detail
o# integer references COrder,
i# integer references Item,
quantity integer check (quantity > 0),
sup integer check ( sup > 0 ), //selling unit price
primary key (o#,i#)
);

CREATE TABLE Restock(
odate date,
i# integer references Item,
primary key(odate, i#)
);

[jkoopmann]

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 (sysdate, :new.i#);
end if;
end if;
end;
/

[thyduong]

thanks a lot

[thyduong]

i got "trigger created" However, when i tried to insert

SQL> insert into odetail values ( 1, 25, 15, 3);
insert into odetail values ( 1, 25, 15, 3)
*
ERROR at line 1:
ORA-04098: trigger 'SYSTEM.INSERT_ODETAIL' is invalid and failed re-validation

[jkoopmann]

don't know,
re-post the trigger and table ddl.
mine worked:
SQL> insert into item values ( 25, '25', 1,1,1,1,1);

1 row created.

SQL> insert into odetail values ( 1, 25, 15, 3);

1 row created.

[thyduong]

i tried to drop everything and repost the table ddl.

SQL> insert into item values ( 25, '25', 1,1,1,1,1);

1 row created.

SQL> insert into odetail values ( 1, 25, 15, 3);
insert into odetail values ( 1, 25, 15, 3)
*
ERROR at line 1:
ORA-04098: trigger 'SYSTEM.INSERT_ODETAIL' is invalid and failed re-validation


SQL> sho err;
Errors for TRIGGER INSERT_ODETAIL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: Encountered the symbol "FOR" when expecting one of the
following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

[jkoopmann]

re-post your trigger code.
looks like it is not compiling.
make sure is says 'trigger created' after you create it.

[thyduong]

yes, the trigger worked. but when i repost every thing. i got the "warning: trigger created with compilation error" again.

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 integer 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 integer 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(
odate date,
i# integer references Item,
primary key(odate, i#)
);

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

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 or replace TRIGGER insert_odetail
before insert on ODetail
declare my_qoh Item.qoh%type;
my_rop Item.rop%type;
for each row
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 (sysdate, :new.i#);
end if;
end if;
end;
/

[jkoopmann]

this is not the trigger code i gave back to you.

[thyduong]

it works now. thanks a billion.