trigger problem

[joy]

I am writting a trigger, I have table parts(PNO,..,QO H,..), before
delete the PNO row, I need to check QOH. Part of the trigger code
like:
create or replace trigger parts_bef_del_r ow
before delete on parts
for each row

declare
num integer := 0;

cursor plc is
select *
from parts
where pno = :old.pno;

When I tried the trigger, it gave me:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/19 PLS-00049: bad bind variable 'OLD.PNO'

In fact my old.pno is not on line 7 at all, what is wrong, please help
and thanks.

[Frank]

Re: trigger problem

joy wrote:

I am writting a trigger, I have table parts(PNO,..,QO H,..), before
delete the PNO row, I need to check QOH. Part of the trigger code
like:
create or replace trigger parts_bef_del_r ow
before delete on parts
for each row
>
declare
num integer := 0;
>
cursor plc is
select *
from parts
where pno = :old.pno;
>
When I tried the trigger, it gave me:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/19 PLS-00049: bad bind variable 'OLD.PNO'
>
In fact my old.pno is not on line 7 at all, what is wrong, please help
and thanks.

:old.pno is not known in the declaration section.
Alos, I'm not sure you want all parts - you probably want to know
if part still exist.
Use something like:
execute immediate 'select count(*) from parts where pno = :b '
into <a variable>
using :old.pno;

Or declare your cursor with another variable
or...
--
Regards, Frank van Bortel