Problem in Inserting a Record [message #524816] |
Tue, 27 September 2011 01:47 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/8f1b11ca9bd92f11afac90dfc96900b3?s=64&d=mm&r=g) |
shanie
Messages: 118 Registered: January 2011
|
Senior Member |
|
|
Dear All,
There is a problem in Inserting a Record.
I have a table with primary key as Student_id
I created a form based on this table.
When I insert the record through form it gives me error
'frm-40508 unable to insert a record'
When I remove primary key from table Then It is inserting TWO Records at a time with the same Student ID.
I checked Data types of form and table it is ok.
Dont know what's the problem.
Table name is GZBGSSP and Fields are below
GZBGSSP_STU_ID VARCHAR2(9 BYTE)
GZBGSSP_PIDM NUMBER(8,0)
GZBGSSP_DEG_PLAN_IND VARCHAR2(15 BYTE)
GZBGSSP_DEG_PLAN_DATE DATE
GZBGSSP_COM_EXAM_IND VARCHAR2(15 BYTE)
GZBGSSP_COM_EXAM_DATE DATE
GZBGSSP_FT_RESD_IND VARCHAR2(15 BYTE)
GZBGSSP_FT_RESD_DATE DATE
Written Code in key_commit Trigger
declare tGZBGSSP_STU_ID saturn.GZBGSSP.GZBGSSP_STU_ID%TYPE;
begin
select GZBGSSP_STU_ID into tGZBGSSP_STU_ID from saturn.GZBGSSP where GZBGSSP_STU_ID = :KEY_ID;
if sql%found then
message('This Student ID already exists!');
clear_block(no_commit);
go_block('dual');
go_item('KEY_ID');
end if;
EXCEPTION WHEN NO_DATA_FOUND THEN
Declare vspriden_pidm SPRIDEN.SPRIDEN_PIDM%TYPE;
BEGIN
SELECT SPRIDEN_PIDM INTO vspriden_pidm FROM SPRIDEN WHERE SPRIDEN_ID = :DUAL.KEY_ID and spriden_change_ind is null;
IF SQL%FOUND THEN
begin
insert into saturn.GZBGSSP(GZBGSSP_STU_ID,GZBGSSP_PIDM,GZBGSSP_DEG_PLAN_IND,GZBGSSP_DEG_PLAN_DATE,GZBGSSP_COM_EXAM_IND,GZBGSSP_COM_EXAM_DATE,GZBGSSP_FT_RESD_IND,GZBGSSP_FT_RESD_DATE)
values(:KEY_ID,vspriden_pidm,:GZBGSSP_DEG_PLAN_IND,:GZBGSSP_DEG_PLAN_DATE,:GZBGSSP_COM_EXAM_IND,:GZBGSSP_COM_EXAM_DATE,:GZBGSSP_FT_RESD_IND,:GZBGSSP_FT_RESD_DATE);
commit;
end;
clear_form(no_commit);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Message('Invalid Id');
END;
end;
Please Help!
Thanks & Regards,
Shanie
|
|
|
|
|
|
|
Re: Problem in Inserting a Record [message #524846 is a reply to message #524833] |
Tue, 27 September 2011 03:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/166871.jpg) |
Baranor
Messages: 83 Registered: September 2011 Location: Netherlands
|
Member |
|
|
Multiple options, WHEN-VALIDATE-ITEM being the best... you can also use the WHEN-NEW-RECORD trigger, but you'll run the risk of locking yourself into a record if accidentially clicking on it. PRE-UPDATE and PRE-INSERT also are options.
|
|
|