如何为库存检查/控制构建SQL触发器 [英] How to build a SQL trigger for stock check/control
问题描述
很棒的人, Great people, 这篇关于如何为库存检查/控制构建SQL触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
看到这里的人帮助别人解决他们的挣扎真的很棒。我有一个似乎没有用的小问题。我试着用Google搜索找到解决方案,这就是我得到了多远。
我想要的是什么:
如果客户订购产品X. SQL触发器必须检查库存中是否有足够的产品X,如果库存不足则应该提供错误,说明库存不足。
示例:客户订购5辆自行车,但库存只有4件:SQL说:库存不足。
< pre lang =SQL> alter PROCEDURE spNewProducts
(
@ descrition VARCHAR ( 255 ),
@ stock INTEGER,
@ distrubtur VARCHAR ( 128 )
)
AS
BEGIN
从产品中选择 *
DECLARE @ Productnr INT
SELECT @ Prodcutnr = MAX(productnr)+ 1 FROM 产品
INSERT INTO 产品(productnr,descrition,stock,distrubtur)
VALUES ( @ productnr , @ descrition , @ stock , @ distrubtur 跨度>);
END )
GO
创建 表 订单
(
Order_id INTEGER,
Customer_id INTEGER,
orderdate DATE
约束 pk_Order
primary key (Order_id)
);
GO
创建 table Order_row
(
Order_id INTEGER,
Product_id INTEGER,
quantity INTEGER
)
go
创建 table ProductPrice
(
Productnr INTEGER,
价格 DECIMAL ( 5 , 2 ),
begindate DATE ,
Enddate DATE ,
)
DROP FUNCTION function () CASCADE ;
CREATE 或 REPLACE 功能 function ()
RETURNS TRIGGER AS $ trigger $
BEGIN
更新产品 SET
stock = COALESCE(股票, 0 ) - Order_row.quantity
WHERE Order_row.productnr = products.productnr;
END ;
创建 TRIGGER 触发器
AFTER INSERT ON 订单
FOR 每行
EXECUTE PROCEDURE function ();
我做错了什么人?
谢谢! trigger
BEGIN
更新产品 SET
stock = COALESCE(股票, 0 ) - Order_row.quantity
WHERE Order_row .productnr = products.productnr;
END ;
创建 TRIGGER 触发器
AFTER INSERT ON 订单
FOR 每行
EXECUTE PROCEDURE function ();
我做错了什么人?
谢谢!
你可以从这些页面开始寻求初步帮助。
触发器简介 - 第一部分 [ ^ ]
触发器 - SQL Server [ ^ ]
但是我认为在执行INSERT语句之前,更容易在存储过程中执行逻辑。
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!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!You can start with these pages for initial help.
An Introduction to Triggers -- Part I[^]
Triggers -- SQL Server[^]
But I think it is easier to do the logic inside the stored procedure instead, before you execute the INSERT statement.