mysql - 如何在插入表事务数据时创建触发器以减少表项的列数量

Question

我是触发器的新手，但想知道如何在我的数据库中使用触发器。我为交易和项目创建了一个表。进行交易时，项目表应该受到这样的影响，

1. 如果选择交易类型“传出”，那么触发器应该将表项中“数量”列的值减少该值来自表事务的字段数量。
2. 如果选择 transaction_type“incoming”，则触发器应将表项中列“quantity”的值增加表事务中的字段数量值。

这是表格

<强>1。表项

`CREATE TABLE `items` (
 `item_id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(40) NOT NULL,
 `purchase_date` varchar(100) NOT NULL,
 `category_id` int(10) NOT NULL,
 `store_id` int(10) NOT NULL,
 `supplier_id` int(10) NOT NULL,
 `batch_id` int(10) DEFAULT NULL,
 `quantity` bigint(11) NOT NULL,
 PRIMARY KEY (`item_id`),
 KEY `category` (`category_id`),
 KEY `store` (`store_id`),
 KEY `supplier` (`supplier_id`),
 KEY `batch` (`batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8`

<强>2。表事务

CREATE TABLE `transactions` (
 `transaction_id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
 `transaction_date` varchar(100) NOT NULL,
 `item_id` int(10) NOT NULL,
 `batch_id` int(10) NOT NULL,
 `store_id` int(10) NOT NULL,
 `transaction_type` varchar(40) NOT NULL,
 `remarks` longtext NOT NULL,
 `quantity` bigint(11) NOT NULL,
 PRIMARY KEY (`transaction_id`),
 KEY `item` (`item_id`),
 KEY `batch` (`batch_id`),
 KEY `store` (`store_id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8

这是我尝试过的方法，但是当我运行事务而不是减少或添加表项中的列数量时，它返回 0:

CREATE TRIGGER subtract_quantity AFTER INSERT ON transactions 
FOR EACH ROW UPDATE items 
SET items.quantity = items.quantity - transactions.quantity=transactions.quantity 
WHERE items.item_id = NEW.item_id

Answer 1

这对我有用

CREATE TRIGGER `subtract_quantity` AFTER INSERT ON `transactions`
 FOR EACH ROW BEGIN

      UPDATE items 

      SET items.quantity =  GREATEST(0, items.quantity - NEW.quantity)
      WHERE items.item_id = NEW.item_id
      and items.quantity > 0;
      
      UPDATE batches 
      SET batches.balance = GREATEST(0, batches.balance - NEW.quantity)
      WHERE batches.batch_id = NEW.batch_id
       and batches.balance > 0;
 END