5 Replies Latest reply on Jan 22, 2016 4:57 PM by Brian Jeffries-Oracle

    I cannot edit the rows in a view with instead of triggers

    215ce137-924d-4d20-aaec-018059e9d452

      Hi,

       

      I have a view which is not inherently updatable.

      So I have defined instead of triggers for delete, insert and update which are working fine when used directly via SQL.

      My problem is that I am still not able to edit the views data using SqlDevelopers 'data' tab.

      All field editors are disabled.

       

      Is there someone out there who knows how I can get SqlDeveloper persuaded to let me directly edit the data in the 'data' tab of a view based on 'instead of' triggers?

       

      Matthias Müller


        • 1. Re: I cannot edit the rows in a view with instead of triggers
          I have a view which is not inherently updatable.

          So I have defined instead of triggers for delete, insert and update which are working fine when used directly via SQL.

          My problem is that I am still not able to edit the views data using SqlDevelopers 'data' tab.

          All field editors are disabled.

           

          Is there someone out there who knows how I can get SqlDeveloper persuaded to let me directly edit the data in the 'data' tab of a view based on 'instead of' triggers?

          I don't know if Sql Dev supports, or intends to support, updating such views.

           

          If the field editors are 'disabled' then Sql Dev does NOT consider the view to be updateable.

           

          Even if the field editors are 'enabled' there can be at least TWO problems:

           

          1. Some field editors may not match the actual underlying datatype definition

          2. Trying to save the data may fail because there is no ROWID to satisfy the RETURNING INTO clause.

           

          Try example 9-2 'INSTEAD OF Triggers in the docs.

          https://docs.oracle.com/database/121/LNPLS/triggers.htm#CIHFGDJG

           

          That example uses the CUSTOMERS and ORDERS table in the sample OE schema. The doc shows an insert of a new row:

          INSERT INTO order_info VALUES

            (999, 'Smith', 'John', 2500, TO_DATE('13-MAR-2001', 'DD-MON-YYYY'), 0);

          If you select that view in Sql Dev (4.1.1.19.59) you can insert a new row and the editors are enabled. But you will only be able to enter one character for the first and last name columns.

           

          Then if you try to commit you will get an exception and the log will show this:

          BEGIN INSERT INTO "OE"."ORDER_INFO" (CUSTOMER_ID, CUST_LAST_NAME, CUST_FIRST_NAME, ORDER_ID, ORDER_DATE, ORDER_STATUS) VALUES (:v1, :v2, :v3, :v4, TO_TIMESTAMP(:v5, 'YYYY-MM-DD HH24:MI:SS.FF'), :v6) RETURNING ROWID INTO :sqldevgridrowid; END;

          See that RETURNING ROWID clause in the last line?

           

          A view does NOT have a ROWID to return. In fact that sample view actually inserts data into TWO different tables. Such a view could actually do DML on any number of tables.

           

          The Sql Dev team is the only one that can tell you if the product is INTENDED to support such views.

           

          But my 'guess' is that the answer will be NO.

          • 2. Re: I cannot edit the rows in a view with instead of triggers
            Gary Graham-Oracle

            If you open your view and look at the last 3 columns of the Columns tab there, what does it say?  Are none of your view's columns marked as modifiable, either via insert, update, or delete?

            • 3. Re: I cannot edit the rows in a view with instead of triggers
              215ce137-924d-4d20-aaec-018059e9d452

              All columns are marked to be modifiable via all three operations, so it's just YES-es in all three columns.

              • 4. Re: I cannot edit the rows in a view with instead of triggers
                215ce137-924d-4d20-aaec-018059e9d452

                Hi,

                 

                Thanks for your comprehensive reply!

                 

                There are just two things I am wondering about:

                1. Sql Developer is able to insert/update/delete the data in an inherently updatable view - why is the missing rowid not an issue with these views?

                2. Instead-of triggers can be used to make views updatable that are not inherently updatable - which part of the behaviour of an inherently updatable view are the instead-of triggers unable to mimic?

                • 5. Re: I cannot edit the rows in a view with instead of triggers
                  Brian Jeffries-Oracle

                  I'm working on data grid editing issues for 4.2. Would it be possible to get specific information for your use case?