Unanswered: Problem inserting data from Oracle Form
Hello! While playing as a newbie with the Forms, I faced the following problem.
I have a table called test. It has 3 columns- account_id, customer_name and account_name. Created a form for the table. Purpose is to learn how to insert into the table using forms. Moreover, I created a PRE-INSERT trigger that will increment the account_id automatically. Here is the trigger-
SELECT AC_seq.nextval INTO :ACCOUNTS.ACCOUNT_ID FROM dual;
On the form I created a button where I put a WHEN-MOUSE-CLICK trigger. The trigger is as follows.
INSERT INTO test (customer_name,account_name) VALUES (:ACCOUNTS.CUSTOMER_NAME,:ACCOUNTS.ACCOUNT_NAME);
But when I queried the table with
SELECT * FROM test;
then the following output appeared-
ACCOUNT_ID CUSTOMER_NAME ACCOUNT_NAME
---------- -------------------- ---------------
Nishat Afza Current
2 Nishat Afza Current
Nasrullah Khaled Liquid
3 Nasrullah Khaled Liquid
though I have entered them just once.
1. What is the problem and how can I solve it?
2. If I would like to increment my account_id column as if the account name is current then it will start with 10001 and if liquid the it will start with 20001, what approach should I take?
Remove the WHEN-MOUSE-CLICK trigger; it is a surplus. As a block already is a DATA BLOCK, saving changes will save two records: once by default (the one entered in the data block) and the other one via INSERT statement in your trigger.
If there was PRIMARY/UNIQUE key (or unique index) there, you'd get an error. Now you only got two "similar" records.
Shortly: let forms do the job whenever you can. Don't code built-in functionality - use it!
As of incrementing account_id column - use a database trigger (instead of Forms one). I must admit I didn't understand the last sentence (the one with 10001 and 20001, liquid and "current" (current what?)).
Thanks little foot. But what if I would like to insert data into that table with that submit button (i mean this is the standard for any front end, isn't it?)? In that case, what I found so far is that I have to create a trigger which will execute that INSERT INTO command. But it is not working in my case. Don't know why.
And the ambiguous question is my fault, sorry for that. Actually, say, in the form, if I put account type as LIQUID, then the id will start incrementing with 100001 and if CURRENT, then it will start increment with 100002. Is there any mechanism I can use there? Well, I can create 4 types of sequences for 4 types of accounts that will start with 100001, 200001, 300001 and 400001. But how can I implement them inside the trigger on submit button? Simply using IF?
And another appended question- if I want to manipulate another table which is not at all present at the form, is it possible to do that? For example, say, my form is only for table named account. can i insert data into another table (every time an id is created on account table, on another table the BALANCE column will be set to 0)?
Once again: get rid of INSERT INTO because you will insert two records. It should be used in a case you mentioned in the third paragraph - to insert a record into a table that is NOT source of the form data block.
Sequences: I'd create two (or as many as needed) and choose which one to use in the DATABASE trigger (not form trigger) (repeating myself once again). One way to choose the right one would be IF-THEN-ELSE, as you've already said.