Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    37

    Red face 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-
    Code:
    BEGIN 
    	 SELECT AC_seq.nextval INTO :ACCOUNTS.ACCOUNT_ID FROM dual; 
    END;
    On the form I created a button where I put a WHEN-MOUSE-CLICK trigger. The trigger is as follows.
    Code:
    BEGIN
    	INSERT INTO test (customer_name,account_name) VALUES (:ACCOUNTS.CUSTOMER_NAME,:ACCOUNTS.ACCOUNT_NAME);
    	COMMIT;
    END;
    But when I queried the table with
    Code:
    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?

    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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?)).

  3. #3
    Join Date
    Mar 2007
    Posts
    37
    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)?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •