关于如何在Oracle Express-SQL中创建.触发更新列 [英] About how to create in Oracle Express - SQL. A trigger to update a column

问题描述

我有一个product表和一个sales表.在product中，我有(product_id, p_desc, qty_stock)，在sales表中，我有(sale_id, producto_id, sale_p_qty)，其中sale_p_qty是客户从某种产品购买的数量.

I have a product table and a sales table. Inside the product I have (product_id, p_desc, qty_stock) and in the sales table I have (sale_id, producto_id, sale_p_qty) where sale_p_qty is the quantity that a customer buys from a certain product.

问题是如何触发更新product表中的qty_stock列值(此值将是库存)?我做不到.我可以做一个递减的触发器，但一次只能递减一个值.正确的方法是获取表sales的sale_p_qty并在qty_stock中递减，但我不能这样做.你能帮我一下吗?

The question would be how can I make a trigger to update the qty_stock column value in the product table (this value would be the stock)? I can not do it. I can do a trigger that decrements, but only one value at a time. The correct one would be to get the sale_p_qty of table sales and decrement in qty_stock but I can not do that. Could you please give me a hand?

我的尝试到此为止(我没有执行任何步骤，在这种情况下我不知道您是否需要这样做):

My attempt stopped at this (I did not do a procedure, I do not know if you need to in this case):

CREATE OR REPLACE TRIGGER trg_stock_ai AFTER INSERT OR UPDATE ON sale
FOR EACH ROW
declare
    new_stock NUMBER (4);
    --SELECT sale_p_qty INTO new_stock FROM sale
    --SET new_stock = (SELECT sale_p_qty FROM sale);
BEGIN
    SELECT sale_p_qty INTO new_stock FROM sale;
    UPDATE product
    SET qty_stock = qty_stock - new_stock
    WHERE product.product_id =: NEW.product_id;
END;

推荐答案

您可以分两个步骤进行操作:

You can do this in two steps:

CREATE OR REPLACE TRIGGER trg_stock_ai AFTER INSERT OR UPDATE ON sale
FOR EACH ROW
BEGIN
    UPDATE product
        SET qty_stock = (qty_stock - :NEW.sale_p_qty)
        WHERE p.product_id = :NEW.product_id;

    UPDATE product
        SET qty_stock = (qty_stock + :OLD.sale_p_qty)
        WHERE p.product_id = :OLD.product_id;
END;

即使在product_id更改的情况下，此方法也适用于插入和更新.

This should work for both inserts and updates, even when product_id changes.

这篇关于关于如何在Oracle Express-SQL中创建.触发更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！