Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This doesn't work because a materialized view is not a view it is a special kind of table: it has data, we can enforce constraints with it, etc. </p> <p>So having got that out of the way let's look at your underlying process logic, which is confusing. You have a materialized view based on a table. Now you want to build a trigger which updates the <em>base table</em> whenever a row is inserted into the materialized view. </p> <ol> <li><p>Do you expect the materialized view to contain the changed data? </p></li> <li><p>Because of the way you have specified the SQL the trigger (if it could work) would update every row in the ORDERS table.</p></li> <li><p>Because the trigger is FOR EACH ROW refreshing the materialized view would update the entire ORDERS table multiple times, once for each row in the ORDERS table.</p></li> <li><p>INSTEAD OF triggers execute the code in the trigger body rather than (instead of) the action specified in the trigger header. So (if it could work) the trigger would update the ORDERS table and insert no rows into the materialized view.</p></li> </ol> <p>So I hope you can see that this error is preventing you from making a more serious architectural error. What you need to do is clarify your business process and then seek to express that in SQL. To my mind, the most appropriate solution would be a BEFORE UPDATE trigger on the ORDERS table. Something like this:</p> <pre><code>CREATE OR REPLACE TRIGGER update_ship_receive BEFORE INSERT or UPDATE ON ORDERS FOR EACH ROW BEGIN if :new.EXPECTED_SHIP_DATE is null then :new.EXPECTED_SHIP_DATE = :new.ORDER_DATE+5; end if; if :new.EXPECTED_RECEIVE_DATE is null then case :new.SHIPPING_METHOD when '1 DAY' then :new.EXPECTED_RECEIVE_DATE = :new.SHIP_DATE+1; when '2 DAY' then :new.EXPECTED_RECEIVE_DATE = :new.SHIP_DATE+2; when 'GROUND' then :new.EXPECTED_RECEIVE_DATE = :new.SHIP_DATE+5; else null; end case; end if; END; / </code></pre> <p>You then just have a simple materialized view with no associated processing to undertake when you refresh it. Obviously your actual business logic may dictate a different solution.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhat is wrong with the following code? It tells me Instead of trigger cannot be created on tables, but I'm creating in on view (Orders_MV)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAPC
    1. USAPC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat is wrong with the following code? It tells me Instead of trigger cannot be created on tables, but I'm creating in on view (Orders_MV)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthankss alott for helping me out. :D
      singulars
      1. PO
      1. USindolent
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload