以下代码有什么问题？它告诉我不是无法在表上创建触发器，而是在视图中创建（Orders_MV） [英] What 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)

问题描述

可能重复：

为什么此触发器失败？它表示无效的标识符。

CREATE MATERIALIZED VIEW ORDERS_MV

BUILD IMMEDIATE

REFRESH COMPLETE ON DEMAND AS

SELECT * FROM ORDERS;

---

---

CREATE OR REPLACE TRIGGER update_ship_receive

INSTEAD OF INSERT ON ORDERS_MV

FOR EACH ROW

BEGIN

  UPDATE ORDERS SET EXPECTED_SHIP_DATE = ORDER_DATE+5;

  UPDATE ORDERS SET EXPECTED_RECEIVE_DATE = SHIP_DATE+1 
WHERE SHIPPING_METHOD = '1 DAY';

  UPDATE ORDERS SET EXPECTED_RECEIVE_DATE = SHIP_DATE+2
 WHERE SHIPPING_METHOD = '2 DAY';

  UPDATE ORDERS SET EXPECTED_RECEIVE_DATE = SHIP_DATE+5 
WHERE SHIPPING_METHOD = 'GROUND';

END;
/

推荐答案

物化视图不是视图，它是一种特殊的表：它具有数据，我们可以对其执行约束，等等。

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.

因此，让我们看一下您的基础流程逻辑，这很令人困惑。您有一个基于表的实例化视图。现在，您想构建一个触发器，该触发器将在将行插入到实例化视图时更新基本表。

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 base table whenever a row is inserted into the materialized view.

1. 您是否希望实例化视图包含已更改的数据？

1. Do you expect the materialized view to contain the changed data?

由于您指定了SQL的方式，触发器（如果可行）将更新ORDERS表中的每一行。

Because of the way you have specified the SQL the trigger (if it could work) would update every row in the ORDERS table.

因为触发器是针对每一行刷新实例化视图，所以会多次更新整个ORDERS表，一次针对ORDERS表中的每一行。

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.

INSTEAD OF触发器执行触发器主体中的代码，而不是（而不是）触发标头中指定的操作。因此（如果可行），触发器将更新ORDERS表，并且不将任何行插入到物化视图中。

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.

因此，我希望您能看到此错误正在阻止您犯更严重的体系结构错误。您需要做的是弄清楚您的业务流程，然后尝试用SQL来表达它。在我看来，最合适的解决方案是在ORDERS表上使用BEFORE UPDATE触发器。像这样的东西：

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:

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;
/

然后，您只有一个简单的物化视图，无需关联处理即可进行操作刷新它。显然，您的实际业务逻辑可能会要求使用其他解决方案。

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.

这篇关于以下代码有什么问题？它告诉我不是无法在表上创建触发器，而是在视图中创建（Orders_MV）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！