CODEWITHSUNDEEP
sqlpostgresql
readytotaste
readytotaste

Reputation: 299

SQL - How can I insert into a table based on a condition?

I have two tables - An orders table and an inventory table. The inventory table keeps track of the product_id, the product_type and the amount of remaining stock. While the orders table keeps track of the order_id, product_id and order_amount. The orders table is joined to the inventory table by product_id.

What is the best way to ensure that I never have an order that comes in which has an order amount > remaining stock for a particular product_id?

It does not seem like I can use an INSERT INTO with a WHERE - wherein I only add orders to the orders table when the order amount < remaining stock amount. Is there another way? Im willing to change the way my tables are set up.

Upvotes: 0

Views: 73

Answers (1)

Adam
Adam

Reputation: 5599

I would add a trigger on the orders table. That trigger would subtract order_amount from the amount of remaining stock. That would automate the calculation of the remaining stock.

Additionally, I would add a check on the remaining stock value to be >= 0. This way you will block an order that would excess remaining stock. The insertion would be rolled-back because it would violate the stock check.

CREATE FUNCTION update_inventory_amount()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE
            inventory
        SET
            amount = amount - NEW.order_amount
        WHERE
            product_id = NEW.product_id;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER update_inventory_amount_trigger
  BEFORE INSERT
  ON orders
  FOR EACH ROW
  EXECUTE PROCEDURE update_inventory_amount();

ALTER TABLE inventory ADD CONSTRAINT amount_check CHECK (amount >= 0);

The above trigger code is just for UPDATE. With a little effort you can extend it to also cover UPDATE and DELETE.

Upvotes: 2

Related Questions