Urgent HELP !! Cant insert a record to other table !
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Urgent HELP !! Cant insert a record to other table !

  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Angry Urgent HELP !! Cant insert a record to other table !

    Hi guys,

    I am really getting frustrated with this PL/SQL code. I thought I could find one of your nice people useful to solve my little problem, that I would probably never end up solving.

    Basiclly, I have 4 tables main ones are [Tenants] & [Payments]

    They both relate, as a Tenant has many payments.

    As I am using an Oracle Forms 6i I think, I am trying to create a Trigger I assume ON_DELETE is right.

    And in the trigger I put a code in it, that when I delete click on the Remove default oracle button that the Tenants & Payment records get inserted into [Pamynet_Histories] table and [Tenants_histories] table.

    But I get this annoying error:

    trigger: ON_DELETE ----inside code



    DECLARE
    ten_rec tenants%ROWTYPE;
    BEGIN
    SELECT * INTO ten_rec
    FROM tenants
    WHERE tenant_id = tenant_id;
    INSERT INTO tenant_histories(H_SURNAME,H_GIVEN_NAME,H_CONTACT_NUM,H_REF_CONTANCT_NUM,H_OCCUPTION_DATE,H_TENANT_C OMP_ADD,H_TENANT_COMP_NAME,TENANT_ID)
    VALUES (ten_rec.surname, ten_rec.given_name, ten_rec.contact_num, ten_rec.ref_contact_num,
    ten_rec.occupation_date, ten_rec.tenant_comp_add,ten_rec.tenant_comp_name,ten_rec.tenant_id);
    END;


    DECLARE
    pay_rec payments%ROWTYPE;
    BEGIN
    SELECT * INTO pay_rec
    FROM payments
    WHERE payment_id = payment_id;
    INSERT INTO payment_histories(H_PAYMENT_TYPE,H_PAYMENT_AMOUNT,H_PAYMENT_DATE,H_PAYMENT_DUE,H_TENANT_ID,PAYMENT_I D,PROPERTY_ID)
    VALUES (pay_rec.payment_type, pay_rec.payment_amount, pay_rec.payment_date, pay_rec.payment_due,pay_rec.tenant_id, pay_rec.payment_id,pay_rec.property_id);
    END;



    ERROR - FRM-1422: ORA-01422: exact fetch returns more
    then requested number of rows

    Please could you solve this problem for me, I am so frustrated about it and I am running out of time, as i have to hand in my project.

  2. #2
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    The select statement returns more than one row. Try doing a Cursor For loop so that you can insert all the records return by the sql statment.

    ex.

    for rec in (select * from tenants)
    loop
    --
    -- your insert statement here.
    --
    end loop;

  3. #3
    Join Date
    Nov 2002
    Posts
    5

    not working still

    No luck at all :( , as i still get the same error.

    Can some one possibly code the correct insert statement for me please?

    for rec in (select * from tenants)
    loop

    DECLARE
    ten_rec tenants%ROWTYPE;
    BEGIN
    SELECT * INTO ten_rec
    FROM tenants
    WHERE tenant_id = tenant_id;
    INSERT INTO tenant_histories(H_SURNAME,H_GIVEN_NAME,H_CONTACT_NUM,H_REF_CONTANCT_NUM,H_OCCUPTION_DATE,H_TENANT_C OMP_ADD,H_TENANT_COMP_NAME,TENANT_ID)
    VALUES (ten_rec.surname, ten_rec.given_name, ten_rec.contact_num, ten_rec.ref_contact_num,ten_rec.occupation_date, ten_rec.tenant_comp_add,ten_rec.tenant_comp_name,ten_rec.tenant_id);
    END;

    end loop;

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    INSERT INTO tenant_histories(H_SURNAME,H_GIVEN_NAME,H_CONTACT_NUM,H_REF_CONTANCT_NUM,H_OCCUPTION_DATE,H_TENANT_C
    OMP_ADD,H_TENANT_COMP_NAME,TENANT_ID)
    SELECT *
    FROM tenants
    WHERE tenant_id = tenant_id


    obviously if the query returns more than one row then you will be inserting several rows

  5. #5
    Join Date
    Nov 2002
    Posts
    5
    hey Pando, I cant seem to compile your code :(

Posting Permissions

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


Click Here to Expand Forum to Full Width