创建一个触发器来更新另一个表中的列时更新表 [英] creating a trigger that updates a table when a column in a different table is updated

问题描述

由于某种原因，我很难完全理解触发器.对于家庭作业，我需要创建一个表，其中包含每种产品的产品ID，总销售额和总销售量(这些列已经在两个不同的表中).然后，我创建一个触发器，当另一个表中的orderplaced列更新为1时，将更新该表.既然我创建的表是空的，由于列是空的，我会做一个分配表建议的UPDATE表还是一个INSERT?如果有人能把我带到正确的方向，我将不胜感激.

For some reason I'm having a hard time fully understanding triggers. For my homework assignment I need to create a table that holds product id, total sales, and total quantity sold for each product (these columns are already in two different tables). Then I create a trigger that updates this table when the orderplaced column from a different table is updated to 1. Not exactly sure where to start. Since the table I created is empty would I do an UPDATE table as the assignment suggests or an INSERT since the columns are empty? If anyone can put me in the right direction I would really appreciate it..

CREATE TABLE bb_sales_sum (
    idProduct number(2) NOT NULL,
    total number(6,2),
    quantity number); 

CREATE OR REPLACE TRIGGER BB_SALESUM_TRG
    AFTER UPDATE OF orderplaced on bb_basket 
    FOR EACH ROW
    WHEN (NEW.orderplaced = 1)
DECLARE 
    lv_count Number;
BEGIN   
    if :new.orderplaced = 1 then 
        for item in 
            (select idproduct, (quantity * price) AS total, quantity
            from bb_basketitem
            where idbasket = :old.idbasket)
    loop
        select count(*)
        into lv_count
        from bb_sales_sum where idProduct = item.idproduct;

        if lv_count = NULL then
            INSERT INTO bb_sales_sum
            VALUES (item.idproduct, item.total, item.quantity);
        else
            update bb_sales_sum 
            set quantity = item.quantity where
            idProduct = item.idproduct;
        end if;
    end loop;
    end if;

END; 
/

推荐答案

您可以使用MERGE代替更新，如果给定的idproduct尚未存在新行，则会创建新行.这些可用行的数量和总数.

You may use a MERGE in place of update, which will create a new row if there isn't one already for a given idproduct and updates the quantity and total for those rows which are already available.

CREATE OR REPLACE TRIGGER bb_salesum_trg
    AFTER UPDATE OF orderplaced on bb_basket 
      FOR EACH ROW
    WHEN (NEW.orderplaced = 1)

BEGIN   
  MERGE INTO bb_sales_sum t USING 
  ( select :new.idproduct as idproduct ,
           :new.quantity  as quantity,
           :new.total     as total 
           from dual ) s  
     ON (s.idproduct = t.idproduct ) 
         WHEN MATCHED THEN UPDATE
            SET  quantity  = s.quantity,
                 total     = s.total
          WHEN NOT MATCHED THEN
     INSERT (
          idproduct,quantity,total)
     VALUES
          ( :new.idproduct,:new.quantity,:new.total );
END;
/

演示

这篇关于创建一个触发器来更新另一个表中的列时更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！