UNTESTED triggers

gistfile1.sql

/* check that there is enough quantity */
CREATE OR REPLACE TRIGGER check_enough_stock
	BEFORE INSERT ON sales
	FOR EACH ROW
DECLARE
	v_qoh product.qoh%TYPE;
	e_not_enough_stock EXCEPTION;
BEGIN
	SELECT qoh INTO v_qoh
	FROM products
	WHERE id = :new.product_id;
	
	IF :new.quantity > v_qoh
		RAISE e_not_enough_stock;
	END IF;
	
	EXCEPTION
		WHEN e_not_enough_stock THEN
			DBMS_OUTPUT.PUT_LINE('ERROR: Not enough stock to add the sale');
END;
	
/* update product qoh */
CREATE OR REPLACE TRIGGER update_qoh
	AFTER INSERT ON sales
	FOR EACH ROW
DECLARE
	v_qoh product.qoh%TYPE;
	v_new_qoh product.qoh%TYPE;
BEGIN
	SELECT qoh INTO v_qoh
	FROM products
	WHERE id = :new.product_id;
	
	v_new_qoh := v_qoh - :new.quantity;
	
	UPDATE products
	SET qoh = :v_new_qoh
	WHERE id = :new.product_id;
END;

/* add sale to despatch table */
CREATE OR REPLACE TRIGGER update_qoh
	AFTER INSERT ON sales
	FOR EACH ROW
DECLARE
	v_product_name products.name%TYPE;
	v_shelf_id products.shelf_id%TYPE;
BEGIN
	INSERT INTO despatch
	( product_name, shelf_id, sale_id, quantity )
	VALUES( :v_product_name, :v_shelf_id, :new.id, :new.quantity );
END;