触发选择子记录，将其值相乘并更新父记录 [英] Trigger selecting child records, multiplying their values and updating parent record

问题描述

我是PL/SQL新手，正在为触发器而苦苦挣扎.

I am a PL/SQL newbie and I'm struggling with a trigger.

说明:

我有三个对象-产品，内容，订单.一种产品可以包含多个CONTAINS，而一个ORDER可以具有多个CONTAINS(基本上以前是PRODUCT和ORDER之间的多对多关系).

I have three objects - PRODUCT, CONTAINS, ORDER. One product can have many CONTAINS and one ORDER can have many CONTAINS (basically it used to be Many-to-many relationship between PRODUCT and ORDER).

每个产品都有一个列值"，每个包含有一个列金额".并且每个ORDER都有一个总计"列.

Each Product has a column "value", each CONTAINS has a column "amount" and each ORDER has a column "total".

当我通过创建新的CONTAINS将新产品添加到ORDER时，我想重新计算字段"total".在订单上.

When I add a new PRODUCT to ORDER via creating new CONTAINS, I want to recalculate field "total" on ORDER.

示例:产品X具有值"产品Y的值"为100.我们有一个ORDERO.现在我在Product X和ORDER O之间创建包含金额"列的CONTAINS(数量为200).现在，触发器应乘以5 * 100，并更新ORDER列的"total"(总数为5).到500.然后我在产品Y"和订单O"之间创建包含金额"列的内容.现在，触发器应重新计算5 * 100 + 10 * 200，并更新总计".订单O到2500上的列.

Example: PRODUCT X has "value" of 100. PRODUCT Y has "value" of 200. We have an ORDER O. Now I create CONTAINS between Product X and ORDER O with column "amount" of 5. Now the trigger should multiply 5 * 100 and update the ORDER column "total" to 500. Then I create CONTAINS between PRODUCT Y and ORDER O with column "amount" of 10. Now the trigger should recalculate 5 * 100 + 10 * 200 and update the "total" column on ORDER O to 2500.

我的错误触发器:

    create or replace TRIGGER TRIGGER1 
AFTER DELETE OR INSERT OR UPDATE OF AMOUNT, PRODUCT_ID_PRODUCT, ORDER_ID_ORDER ON CONTAINS 
REFERENCING NEW AS n
FOR EACH ROW
DECLARE
value number;
amount number;
total number;
BEGIN
LOOP
FOR emp IN (SELECT AMOUNT, PRODUCT_ID_PRODUCT, ORDER_ID_ORDER FROM CONTAINS WHERE ORDER_ID_ORDER = :n.ORDER_ID_ORDER) 
LOOP
(SELECT SUM(VALUE) into product FROM PRODUCT WHERE ID_PRODUCT = :emp.PRODUCT_ID_PRODUCT);
amount:= emp.AMOUNT;
total:= total + (product * amount);
UPDATE ORDER SET ORDER.TOTAL = total WHERE ID_ORDER = :n.ORDER_ID_ORDER;
END LOOP;
END LOOP;
END;

错误显示在这里:

(从SHERID_PRODUCT =:emp.PRODUCT_ID_PRODUCT的产品中选择SUM(VALUE)到产品中)

(SELECT SUM(VALUE) into product FROM PRODUCT WHERE ID_PRODUCT = :emp.PRODUCT_ID_PRODUCT)

说我不能使用"emp".

saying I can't use "emp".

错误消息:

10/2 PLS-00103:遇到符号"SELECT".当期望以下情况之一时:(-+ case mod new不为null继续平均计数当前存在的最大值最小值先前的sql stddev的总和方差执行所有合并时间时间戳记间隔日期管道<交替10/89 PLS-00103:遇到符号)"预期以下情况之一时:.(* @％&-+ +/，用于mod余数rem<指数(**)>和或具有相交的负序的组开始并集，其中||指示符多集15/5 PLS-00103:遇到符号"LOOP"，当期望以下之一时:;

10/2 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe <an alternat 10/89 PLS-00103: Encountered the symbol ")" when expecting one of the following: . ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || indicator multiset 15/5 PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: ;

推荐答案

通过删除实际上不需要的循环/光标来简化触发器.

Simplified the trigger by removing loops/cursors that isn't actually required.

create or replace TRIGGER TRIGGER1 
AFTER DELETE OR INSERT OR UPDATE OF AMOUNT, PRODUCT_ID_PRODUCT, ORDER_ID_ORDER 
ON CONTAINS 
REFERENCING NEW AS n
FOR EACH ROW
DECLARE

lv_total number;

BEGIN

SELECT SUM(prdt.VALUE * :n.amount) into lv_total 
FROM PRODUCT prdt where prdt.ID_PRODUCT = :n.PRODUCT_ID_PRODUCT;


UPDATE ORDERs SET TOTAL = lv_total WHERE ID_ORDER = :n.ORDER_ID_ORDER;

END;

请参阅DB Fiddle链接以获取解决方案: https://dbfiddle.uk/?rdbms = oracle_11.2& fiddle = 3be867f6ab2e93978ae45a7d305434a1

Refer DB Fiddle link for solution :https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3be867f6ab2e93978ae45a7d305434a1

PS:在触发器中的DML调整得不够好时，触发器可能会导致性能瓶颈.建议检查触发器中的SELECT，INSERT，UPDATE语句的解释计划并根据需要进行调整.对于CONTAINS.ORDER_ID_ORDER和PRODUCTS.ID_PRODUCT不可用，创建一个将是有益的，但建议向DBA负责人咨询.

PS:Triggers can cause performance bottleneck at time when the DMLs in the triggers are not tuned well enough.Recommendation is to check the explain plan for SELECT,INSERT,UPDATE statements inside a trigger and tune them as desired.If Indexes are not available for CONTAINS.ORDER_ID_ORDER and PRODUCTS.ID_PRODUCT creating one would be beneficial but would recommend consulting with DBA in-charge.

更新:现在，由于您需要从触发触发器的表中进行选择，因此我们不得不忍受着著名的Mutating触发器错误 ORA-04091:表MYTABLE.CONTAINS正在变异，trigger/，幸运的是Oracle提供了一个简单的解决方案为此，它使用了从 Oracle Database 11g Release1 版本开始添加的 Compound触发器.

UPDATE : Now since you need to Select from the table on which trigger is fired we have to live with famous Mutating trigger error ORA-04091: table MYTABLE.CONTAINS is mutating, trigger/ and luckily Oracle has an easy solution for it using Compound trigger that was added from Oracle Database 11g Release1 version onwards.

有关复合触发器的更多详细信息和技术说明，您可以参考 http://stevenfeuersteinonplsql.blogspot.com/2016/12/get-rid-of-mutating-table-trigger.html

For more details and technical explanation on Compound Trigger you may refer http://stevenfeuersteinonplsql.blogspot.com/2016/12/get-rid-of-mutating-table-trigger.html

触发代码是这样的，ta.da ..因此，我们将行带到pl/sql表中进行行操作，并对pl/sql表中的每一行执行语句操作.

Trigger Code goes like this, ta.da.. So we take rows to a pl/sql table for row operation and perform statement operation for each of the rows from the pl/sql table.

CREATE OR REPLACE TRIGGER trigger2    
FOR UPDATE OR INSERT ON contains    
COMPOUND TRIGGER     

   TYPE typ_contains IS TABLE OF contains%rowtype  INDEX BY PLS_INTEGER;    
   tab_contains   typ_contains;    
    
   AFTER EACH ROW IS    
   BEGIN  
      tab_contains (tab_contains.COUNT + 1).amount :=    
           :NEW.amount;    
      tab_contains (tab_contains.COUNT).product_id_product := :NEW.product_id_product;
      tab_contains (tab_contains.COUNT).order_id_order := :NEW.order_id_order;
      
   END AFTER EACH ROW;    
    
   AFTER STATEMENT IS    
   lv_total number;
   
   BEGIN        
       
      FOR indx IN 1 .. tab_contains.COUNT    
      LOOP   
      
       SELECT SUM(prdt.VALUE * tab_contains(indx).amount) into lv_total 
       FROM PRODUCT prdt,contains cnts
       where cnts.order_id_order = tab_contains(indx).order_id_order 
       and prdt.id_product = cnts.product_id_product;

      UPDATE ORDERs SET TOTAL = lv_total 
      WHERE ID_ORDER = tab_contains(indx).ORDER_ID_ORDER;
                                     
      END LOOP;    
   END AFTER STATEMENT;    
END trigger2; 
/

可以在DBfiddle链接 https://dbfiddle.uk/中找到更新的解决方案.?rdbms = oracle_11.2& fiddle = 1fb40eef7cf3a647bc5560ed19490240

Updated solution can be found in DBfiddle link https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=1fb40eef7cf3a647bc5560ed19490240

这篇关于触发选择子记录，将其值相乘并更新父记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！