473,320 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

trigger problem

joy
I am writting a trigger, I have table parts(PNO,..,QOH,..), before
delete the PNO row, I need to check QOH. Part of the trigger code
like:
create or replace trigger parts_bef_del_row
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.
Jul 19 '05 #1
1 3568
joy wrote:
I am writting a trigger, I have table parts(PNO,..,QOH,..), before
delete the PNO row, I need to check QOH. Part of the trigger code
like:
create or replace trigger parts_bef_del_row
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

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Galina | last post by:
Hello I work with Oracle 9 database. I want to create a trigger using 2 tables: KEY_SKILLS_STUDENT and KEY_SKILLS. There are fields in KEY_SKILLS_STUDENT: KEY_SKILLS_ID, PORTFOLIO_RESULT and...
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
13
by: Tolik Gusin | last post by:
Hello All, DB2 UDB 8.1 FP3 for Linux The table has 4 triggers on the Insert operation. Three triggers small (200 bytes) and one trigger large (3 ËÂ). In the large trigger there is one long...
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
5
by: William of Ockham | last post by:
Hi, I was asked to recreate a new clean database for our developers because the current one they use is not entirely up to date. So I created a new database and I run into the followin strange...
2
by: gustavo_randich | last post by:
Hi :-) I'm porting a project from Oracle to DB2 and now I'm trying to avoid error SQL0746N in a trigger which reads the same table in which the trigger is defined. Below is Oracle's...
12
by: Bob Stearns | last post by:
I am trying to create a duplicate prevention trigger: CREATE TRIGGER is3.ard_u_unique BEFORE UPDATE OF act_recov_date ON is3.flushes REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN...
2
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is...
2
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL parent_sample_id int NOT NULL ....lots of other...
1
by: veasnamuch | last post by:
I have a problem while I create a trigger to my table. My objective is getting any change made to my table and record it in to another table . My have thousands records before I add new trigger to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.