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.

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;

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;

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

hey Pando, I cant seem to compile your code :(