MYSQL 触发器选择和更新在同一触发器中

Question

OK，我试图从一个表中读取值，并根据该值更新另一个表中的值。换句话说，我需要一个表中的数量从另一个表中的相同零件号中减去。也许有更好的方法，但我试着用触发器来做。**当我运行它作为一个查询它工作得很好。然而，作为一个触发器就不那么好了。如果能帮点忙我会很感激的。由于

Create Trigger Quantity
After INSERT
ON Trans for each ROW
BEGIN
select Product, Qty into @A1,@A2
From Trans
order by TransID desc
limit 1;
Select Parts.Part_ID Into @A3
From Parts
Where Parts.PartNo = @A1;
Update Parts
set Parts.Qty_OnHand = Parts.Qty_OnHand - @A2
Where Parts.Part_ID = @A3;
end;

---

由于语法原因，它将不允许我创建触发器。

Trans
`TransID` int(11) NOT NULL,
`Date_Time` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
`Cap_Data` tinyint(1) NOT NULL,
`Job_Number` varchar(20) NOT NULL,
`User` varchar(60) NOT NULL,
`Product` varchar(60) NOT NULL,
`Qty` int(20) NOT NULL,
`Cost` float NOT NULL
Parts
`Part_ID` int(11) NOT NULL,
`Qty_OnHand` int(6) NOT NULL,
`Reorder_Point` int(6) NOT NULL,
`PartNo` varchar(50) NOT NULL,
`Description` varchar(128) NOT NULL,
`Cost` float NOT NULL,
`Note` varchar(250) NOT NULL,
`Category` varchar(20) NOT NULL,
`Vendor` varchar(50) NOT NULL,
`Manufacturer` varchar(50) NOT NULL,
`Hyperlink` varchar(200) NOT NULL,
`Image_Path` varchar(100) NOT NULL,
`UL_Doc` varchar(100) NOT NULL,
`Stock_Item` bit(1) NOT NULL,
`Barcode` varchar(100) NOT NULL,
`UL_File_No` varchar(25) NOT NULL,
`Bin_Loc` varchar(8) NOT NULL

Answer 1

在尝试添加行之前，您应该检查数量是否足够大以进行减少，或者您必须扩展触发器以捕获此情况并引发错误

CREATE tABLE Trans
(`TransID` int(11) NOT NULL,
`Date_Time` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
`Cap_Data` tinyint(1) NOT NULL,
`Job_Number` varchar(20) NOT NULL,
`User` varchar(60) NOT NULL,
`Product` varchar(60) NOT NULL,
`Qty` int(20) NOT NULL,
`Cost` float NOT NULL)

<>以前✓

CREATE TABLE 
Parts
(`Part_ID` int(11) NOT NULL,
`Qty_OnHand` int(6) NOT NULL,
`Reorder_Point` int(6) NOT NULL,
`PartNo` varchar(50) NOT NULL,
`Description` varchar(128) NOT NULL,
`Cost` float NOT NULL,
`Note` varchar(250) NOT NULL,
`Category` varchar(20) NOT NULL,
`Vendor` varchar(50) NOT NULL,
`Manufacturer` varchar(50) NOT NULL,
`Hyperlink` varchar(200) NOT NULL,
`Image_Path` varchar(100) NOT NULL,
`UL_Doc` varchar(100) NOT NULL,
`Stock_Item` bit(1) NOT NULL,
`Barcode` varchar(100) NOT NULL,
`UL_File_No` varchar(25) NOT NULL,
`Bin_Loc` varchar(8) NOT NULL)

<>以前✓

INSERT INTO Parts VALUES(1,10,0,0,0,9,9,9,9,9,9,9,9,1,9,9,9)

<>以前✓

Create Trigger Quantity
After INSERT
ON Trans for each ROW
Update Parts
set Qty_OnHand = Qty_OnHand - NEW.Qty 
Where Part_ID = (Select Part_ID 
From (SELECT * FROM Parts) p
Where PartNo = NEW.Product);

<>以前✓

INSERT INTO Trans VALUES (1,now(),1,1,1,0,1,1)

<>以前✓

SELECT * FROM Parts

Part_ID | Qty_OnHand | Reorder_Point | PartNo |描述|成本|说明|类别|供应商|制造商|超链接| Image_Path | UL_Doc | Stock_Item |条形码| UL_File_No | Bin_Loc------: | ---------: | ------------: | :----- | :---------- | ---: | :--- | :------- | :----- | :----------- | :-------- | :--------- | :----- | :--------- | :------ | :--------- | :------1 | 9 | 0 | 0 | 0 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 1 | 9 | 9 | 9 | 9

db<此处小提琴>