How to build a SQL trigger for stock check/control

See more:

SQL

trigger

Great people,

It really awesome to see here people helping others out with their struggles. I have a little issue which doesn't seem to be working. I tried and googled to find a solution and this is how far I got.

What I want:
If a customer order a product X. The SQL trigger must check if there is enough of product X in stock and if it is not enough in stock it should give an error saying that there is not enough stock.

Example: Customer orders 5 bicycle but there is only 4 in stock: SQL saying: Stock is not sufficient.

alter PROCEDURE spNewProducts
 (	
	@describtion	VARCHAR(255),
	@stock		INTEGER,
	@distrubtur	VARCHAR(128)
)
AS
BEGIN
   Select * from products
   
      DECLARE @Productnr INT
      SELECT @Prodcutnr = MAX(productnr) + 1 FROM products
  
      INSERT INTO products(productnr, describtion, stock, distrubtur)
     VALUES (@productnr, @describtion, @stock, @distrubtur);

END)

GO
create table Order
(

Order_id		INTEGER,
Customer_id	INTEGER,
orderdate		DATE

constraint pk_Order
	primary key (Order_id)
);

GO

create table Order_row
(

Order_id		INTEGER,
Product_id	INTEGER,
quantity		INTEGER
)

go
create table ProductPrice
(

Productnr		INTEGER,
Price			DECIMAL(5,2),
begindate	        DATE,
Enddate		DATE,
)


DROP FUNCTION function() CASCADE ;
CREATE OR REPLACE FUNCTION function() 
RETURNS TRIGGER AS $trigger$
    BEGIN
      UPDATE product SET
      stock=COALESCE(stock,0)-Order_row.quantity
        WHERE Order_row.productnr=products.productnr;
        END;


CREATE TRIGGER trigger
    AFTER INSERT ON order
    FOR EACH ROW
    EXECUTE PROCEDURE function();

What am i doing wrong guys?
Thank you!