How to insert into a view of joins?

Question

I am trying to insert some data inside a view which contains columns from two tables. The problem is that I recieve the error:

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table

This is my code:

CREATE VIEW testvizualizare AS
SELECT
    F.id_formatie, F.nume nume_formatie, F.data_lansare, F.tara_prov,
    A.data_l, A.gen, A.id_album, A.id_formatie id_formatie_album, A.nume nume_album, A.pret 
FROM
    formatie F JOIN album A ON(F.id_formatie = A.id_formatie) 
    JOIN castiga C ON (C.id_formatie = A.id_formatie)
    JOIN premiu P ON(P.id_premiu = C.id_premiu)
WHERE
    EXISTS(
        SELECT 
            1
        FROM 
            formatie F1 JOIN album A1 ON(F1.id_formatie = A1.id_formatie) 
            JOIN castiga C1 ON (C1.id_formatie = A1.id_formatie)
            JOIN premiu P1 ON(P1.id_premiu = C1.id_premiu)
        WHERE
            f1.id_formatie = F.id_formatie AND LOWER(a1.gen) = 'pop');

INSERT INTO testvizualizare
VALUES(100, 'Atmosphere', (SELECT TO_DATE('01011996', 'DDMMYYYY') FROM DUAL), 'USA', (SELECT TO_DATE('06052014', 'DDMMYYYY') FROM DUAL), 'Hip-Hop', 999, 100, 'Southsiders', 15);

I guess that the problem is the join. I creating two inserts each for each table (F and A). I also tried removing A.id_formatie, but with no luck.

Any help is appreaciated! Thanks.

http://stackoverflow.com/a/1653192/575376 – juergen d May 14 '17 at 11:24 — juergen d, May 14 '17 at 11:24

suchith shivali · Answer 1 · 2017-05-15T00:32:31.090

Yes you are correct. you cannot insert record into complex view(means select query with joins). But you can insert records by creating INSTEAD of trigger on the view.

You can use below code for your issue. But be sure on primary keys that your going to insert, if any on the table.

    CREATE OR REPLACE TRIGGER tr_testvizualizare
    INSTEAD OF INSERT ON testvizualizare
    BEGIN
        insert into formatie(id_formatie, nume nume_formatie, data_lansare, tara_prov) 
                     values (:new.id_formatie,:new.nume nume_formatie,:new.data_lansare,:new.tara_prov);
        insert into album(data_l, gen, id_album, id_formatie id_formatie_album, nume nume_album, pret) 
                     values (new.data_l,:new.gen,:new.id_album,:new.id_formatie id_formatie_album,:new.nume nume_album,:new.pret) ; 

   END;
   /

After creating a trigger on view, try to insert

INSERT INTO testvizualizare
               VALUES(100, 'Atmosphere', (SELECT TO_DATE('01011996', 'DDMMYYYY') FROM DUAL),
                      'USA', (SELECT TO_DATE('06052014', 'DDMMYYYY') FROM DUAL),
                      'Hip-Hop', 999, 100, 'Southsiders', 15);

I hope I answered your question.

How to insert into a view of joins?

1 Answers1