PL/SQL触发器开发咨询：销售订单明细插入触发库存报表生成

Got it, let's tackle this trigger step by step. First, I need to make some reasonable assumptions about your table structures since you didn't provide full schema details—you can tweak these to match your actual database setup.

Assumed Table Structures #

First, let's define the tables we'll work with (adjust column names/types as needed):

• sales_order (sales order header):
  • order_id NUMBER PRIMARY KEY
  • due_date DATE -- This is where we get the reportDate from, after truncating time
• sales_order_detail (sales order line items):
  • detail_id NUMBER PRIMARY KEY
  • order_id NUMBER REFERENCES sales_order(order_id)
  • item_id NUMBER REFERENCES rawmaterials(item_id)
  • quantity NUMBER -- The number of units ordered for this item
• rawmaterials:
  • item_id NUMBER PRIMARY KEY
  • current_stock NUMBER -- We'll use this for opening stock (adjust if you have a historical inventory table)
• inventory_report:
  • report_id NUMBER PRIMARY KEY GENERATED ALWAYS AS IDENTITY -- Auto-incrementing PK
  • item_id NUMBER REFERENCES rawmaterials(item_id)
  • report_date DATE
  • opening_stock NUMBER
  • consumed_qty NUMBER
  • same_day_orders NUMBER
  • next_day_orders NUMBER

The PL/SQL Trigger Code #

Here's the trigger that will handle the insertion into inventory_report whenever a new row is added to sales_order_detail:

CREATE OR REPLACE TRIGGER trg_sales_detail_to_inventory_report
AFTER INSERT ON sales_order_detail
FOR EACH ROW
DECLARE
    v_due_date DATE;
    v_opening_stock NUMBER;
    v_same_day_total NUMBER;
    v_next_day_total NUMBER;
BEGIN
    -- Step 1: Get the truncated due date from the associated sales order
    SELECT TRUNC(s.due_date)
    INTO v_due_date
    FROM sales_order s
    WHERE s.order_id = :NEW.order_id;

    -- Step 2: Get the opening stock for the raw material (adjust if using historical inventory)
    SELECT r.current_stock
    INTO v_opening_stock
    FROM rawmaterials r
    WHERE r.item_id = :NEW.item_id;

    -- Step 3: Calculate total orders for the same report date (including this new entry)
    SELECT COALESCE(SUM(quantity), 0)
    INTO v_same_day_total
    FROM sales_order_detail sod
    JOIN sales_order s ON sod.order_id = s.order_id
    WHERE sod.item_id = :NEW.item_id
      AND TRUNC(s.due_date) = v_due_date;

    -- Step 4: Calculate total orders for the day after report date
    SELECT COALESCE(SUM(quantity), 0)
    INTO v_next_day_total
    FROM sales_order_detail sod
    JOIN sales_order s ON sod.order_id = s.order_id
    WHERE sod.item_id = :NEW.item_id
      AND TRUNC(s.due_date) = v_due_date + 1;

    -- Step 5: Insert the record into inventory_report
    INSERT INTO inventory_report (
        item_id,
        report_date,
        opening_stock,
        consumed_qty,
        same_day_orders,
        next_day_orders
    ) VALUES (
        :NEW.item_id,
        v_due_date,
        v_opening_stock,
        :NEW.quantity, -- Assuming consumed quantity is the quantity from this new detail line
        v_same_day_total,
        v_next_day_total
    );
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle cases where the sales order or raw material doesn't exist (adjust error handling as needed)
        RAISE_APPLICATION_ERROR(-20001, 'Associated sales order or raw material not found for the new detail line.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, 'Error inserting into inventory_report: ' || SQLERRM);
END;
/

Key Explanations #

Let's break down what each part does:

• Trigger Timing: AFTER INSERT ensures we only run this logic after the new detail line is successfully added to sales_order_detail.
• :NEW Pseudorecord: This gives us access to the values of the newly inserted row in sales_order_detail (like :NEW.item_id and :NEW.order_id).
• Truncating Dates: TRUNC(s.due_date) removes the time component from the due date, so we group all orders by the calendar day.
• COALESCE: We use this to handle cases where there are no orders for the same day or next day—this ensures we get 0 instead of NULL.
• Error Handling: The EXCEPTION block catches common issues (like missing sales orders/raw materials) and raises meaningful error messages.

Important Notes to Adjust for Your Setup #

• Opening Stock Calculation: If you have a historical inventory table that tracks stock levels by date, replace the SELECT r.current_stock query with one that fetches the stock level as of the start of v_due_date.
• Consumed Quantity: If consumed_qty should be the total daily consumption instead of just this line item's quantity, replace :NEW.quantity with v_same_day_total.
• Avoiding Duplicate Reports: If you want only one inventory_report record per item per day (instead of one per detail line), you'd modify the trigger to check if a record already exists for :NEW.item_id and v_due_date. If it does, use UPDATE instead of INSERT; if not, proceed with INSERT. Here's a quick snippet for that:

  -- Replace the INSERT block with this:
  MERGE INTO inventory_report ir
  USING (
      SELECT :NEW.item_id AS item_id, v_due_date AS report_date FROM DUAL
  ) src
  ON (ir.item_id = src.item_id AND ir.report_date = src.report_date)
  WHEN MATCHED THEN
      UPDATE SET
          opening_stock = v_opening_stock,
          consumed_qty = ir.consumed_qty + :NEW.quantity,
          same_day_orders = v_same_day_total,
          next_day_orders = v_next_day_total
  WHEN NOT MATCHED THEN
      INSERT (item_id, report_date, opening_stock, consumed_qty, same_day_orders, next_day_orders)
      VALUES (:NEW.item_id, v_due_date, v_opening_stock, :NEW.quantity, v_same_day_total, v_next_day_total);
  

内容的提问来源于stack exchange，提问作者Oat