asi
asked on
Oracle && BDE
What component should i use to define new stored procedure (Oracle) with BDE components ?
I try to define the following sql :
CREATE OR REPLACE TRIGGER triggername
BEFORE INSERT
ON my_table --must exist, create it if not
FOR EACH ROW
BEGIN
IF ID IS NULL THEN
SELECT my_seq.nextval -- must exist, create it if not
INTO :NEW.ID -- :NEW missed
FROM DUAL;
END IF;
END;
in TQuery.sql property and tQuery.execSql
and got error :
Query Field NEW is from unknown type ...
I try to define the following sql :
CREATE OR REPLACE TRIGGER triggername
BEFORE INSERT
ON my_table --must exist, create it if not
FOR EACH ROW
BEGIN
IF ID IS NULL THEN
SELECT my_seq.nextval -- must exist, create it if not
INTO :NEW.ID -- :NEW missed
FROM DUAL;
END IF;
END;
in TQuery.sql property and tQuery.execSql
and got error :
Query Field NEW is from unknown type ...
btw.
this
IF ID IS NULL THEN
should be
IF :NEW.ID IS NULL THEN
meikl ;-)
this
IF ID IS NULL THEN
should be
IF :NEW.ID IS NULL THEN
meikl ;-)
ASKER
Ok, i finally success to do the following steps:
1)
create table try4 (id number,text_col varchar2(80))
2)
CREATE OR REPLACE TRIGGER Inc_Field_onTry4
BEFORE INSERT
ON try4 --must exist, create it if not
FOR EACH ROW
BEGIN
IF :NEW.ID IS NULL THEN
SELECT my_seq.nextval -- must exist, create it if not
INTO :NEW.ID -- :NEW missed
FROM DUAL;
END IF;
END;
But when i try to execute
INSERT INTO TRY4 (text_col) VALUES('as111')
i got ORA-04098
why ?
;-)asi
1)
create table try4 (id number,text_col varchar2(80))
2)
CREATE OR REPLACE TRIGGER Inc_Field_onTry4
BEFORE INSERT
ON try4 --must exist, create it if not
FOR EACH ROW
BEGIN
IF :NEW.ID IS NULL THEN
SELECT my_seq.nextval -- must exist, create it if not
INTO :NEW.ID -- :NEW missed
FROM DUAL;
END IF;
END;
But when i try to execute
INSERT INTO TRY4 (text_col) VALUES('as111')
i got ORA-04098
why ?
;-)asi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
here you can download the trial from toad
http://www.quest.com/requests/?RequestDefID=49
if you have it, you can examine
your trigger and find out what is missed
meikl ;-)
http://www.quest.com/requests/?RequestDefID=49
if you have it, you can examine
your trigger and find out what is missed
meikl ;-)
hello?
use sql+ instead