(5)创建入库表出库表上的六个触发器。mysql的代码
时间: 2023-06-19 12:10:06 浏览: 54
下面是创建入库表和出库表上的六个触发器的示例MySQL代码:
1. 在入库表上创建“更新库存”触发器:
```mysql
CREATE TRIGGER update_stock_in
AFTER INSERT ON stock_in
FOR EACH ROW
UPDATE products SET stock = stock + NEW.quantity WHERE product_id = NEW.product_id;
```
2. 在入库表上创建“更新成本”触发器:
```mysql
CREATE TRIGGER update_cost_in
AFTER INSERT ON stock_in
FOR EACH ROW
UPDATE products SET cost = (cost * stock + NEW.cost * NEW.quantity) / (stock + NEW.quantity) WHERE product_id = NEW.product_id;
```
3. 在入库表上创建“更新平均售价”触发器:
```mysql
CREATE TRIGGER update_avg_price_in
AFTER INSERT ON stock_in
FOR EACH ROW
UPDATE products SET avg_price = (avg_price * stock + NEW.price * NEW.quantity) / (stock + NEW.quantity) WHERE product_id = NEW.product_id;
```
4. 在出库表上创建“更新库存”触发器:
```mysql
CREATE TRIGGER update_stock_out
AFTER INSERT ON stock_out
FOR EACH ROW
UPDATE products SET stock = stock - NEW.quantity WHERE product_id = NEW.product_id;
```
5. 在出库表上创建“更新销售额”触发器:
```mysql
CREATE TRIGGER update_sales_out
AFTER INSERT ON stock_out
FOR EACH ROW
UPDATE products SET sales = sales + NEW.price * NEW.quantity WHERE product_id = NEW.product_id;
```
6. 在出库表上创建“更新平均成本”触发器:
```mysql
CREATE TRIGGER update_avg_cost_out
AFTER INSERT ON stock_out
FOR EACH ROW
UPDATE products SET avg_cost = (avg_cost * stock + NEW.cost * NEW.quantity) / (stock - NEW.quantity) WHERE product_id = NEW.product_id;
```