| Problem with insertion of a new record [message #334650] |
Thu, 17 July 2008 09:58  |
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
|
Member |
|
|
Hi Friends,
I have a data block in which I can display a number of records. In this data block the user will be able to edit the fields if no child records are found in another table. I have used when-new-record-instance to attain this scenario. All are text items. One item licensee_id which is made invisible by setting the property in property palette and required=no ( as this is the primary key of the table). Also the audit columns are made invisible.
The code for it is as follows:
DECLARE
v_alert_button NUMBER;
v_cnt NUMBER;
BEGIN
SELECT COUNT (*)
INTO v_cnt
FROM id_rev_contracts
WHERE licensee_id = :ID_REV_LICENSEES.licensee_id;
IF v_cnt > 0 THEN
set_item_property('ID_REV_LICENSEES.LICENSEE_NAME', UPDATE_ALLOWED, PROPERTY_FALSE);
ELSE
set_item_property('ID_REV_LICENSEES.LICENSEE_NAME', UPDATE_ALLOWED, PROPERTY_TRUE);
-- set_item_property('ID_REV_LICENSEES.LICENSEE_NAME', INSERT_ALLOWED, PROPERTY_TRUE);
END IF;
END;
Now in this data block I should also be able to insert new records and for the same I have used PRE-INSERT trigger and the code for it is as follows:
DECLARE
v_alert_button NUMBER;
CURSOR v_licensee_id IS SELECT id_rev_licensees_s.NEXTVAL FROM dual;
BEGIN
OPEN v_licensee_id;
FETCH v_licensee_id INTO :id_rev_licensees.licensee_id;
CLOSE v_licensee_id;
IF :id_rev_licensees.licensee_id IS NULL THEN
Message('Error Generating Next v_licensee_id');
RAISE Form_Trigger_Failure;
END IF;
:ID_REV_LICENSEES.created_by := :GLOBAL.g_login_name;
:ID_REV_LICENSEES.last_updated_by := :GLOBAL.g_login_name;
:ID_REV_LICENSEES.create_date := SYSDATE;
:ID_REV_LICENSEES.last_update_date := SYSDATE;
EXCEPTION
WHEN form_trigger_failure
THEN
RAISE form_trigger_failure;
WHEN OTHERS
THEN
v_alert_button :=
msgbox ('ERROR in Pre-Insert - ' || SQLERRM, 'STOP', 'Contact IST');
RAISE form_trigger_failure;
END;
Every thing is compiling fine but at the run time when I am trying to insert a new record I am receiving the following error:
FRM-40508:ORACLE error:unable to insert record
I also think the pre-insert record is not firing at the time of inserting a new record and saving it. So I request you to please delve into this problem and suggest me how to overcome this problem. Code snippets would do more help for me. If you need any other things from me please let me know. I will see if I could be of any help in that concern because I may not be able to send the entire form as it is. I also request senior member like David to look into this problem.
Thanks and regards,
Vamsi K Gummadi.
|
|
|
|
|
|
| Re: Problem with insertion of a new record [message #334755 is a reply to message #334729] |
Thu, 17 July 2008 16:57  |
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
|
Member |
|
|
Hi Little Foot,
Thank you for your suggestion and it helped me.
I have modified the code in Pre-Insert trigger as follows:
DECLARE
v_alert_button NUMBER;
BEGIN
SELECT id_rev_licensees_s.NEXTVAL
INTO :ID_REV_LICENSEES.LICENSEE_ID
FROM DUAL;
:ID_REV_LICENSEES.created_by := :GLOBAL.g_login_name;
:ID_REV_LICENSEES.last_updated_by := :GLOBAL.g_login_name;
:ID_REV_LICENSEES.create_date := SYSDATE;
:ID_REV_LICENSEES.last_update_date := SYSDATE;
EXCEPTION
WHEN form_trigger_failure
THEN
RAISE form_trigger_failure;
WHEN OTHERS
THEN
v_alert_button :=
msgbox ('ERROR in Pre-Insert - ' || SQLERRM, 'STOP', 'Contact IST');
--GO_ITEM('ID_REV_LICENSEES.COUNTRY_CODE');
RAISE form_trigger_failure;
END;
I think the problem is with | Quote: | :ID_REV_LICENSEES.LICENSEE_ID
|
.
Because if I am using | Quote: | :id_rev_licensees.licensee_id
|
PRE-INSERT trigger is not firing but when I use | Quote: | :ID_REV_LICENSEES.LICENSEE_ID
|
the trigger is firing.
I don't know if it is case sensitive or...? But now it is working.
Thanks you for your suggestions.
Vamsi K Gummadi.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
