为什么说这个触发器包含sql语法错误？(Why does it say this trigger contains sql syntax errors?)

为什么说这个触发器包含sql语法错误？(Why does it say this trigger contains sql syntax errors?)

我已经在mysqlworkbench中编写了一个触发器，它应该在将新行添加到订单表后更新产品库存（我的代码是荷兰语，这就是为什么即时解释这个）但是当我尝试应用它时它不起作用mysqlworkbench说它的sql代码包含错误。

这是触发器：

CREATE DEFINER = CURRENT_USER TRIGGER `winkel`.`bestelregel_AFTER_INSERT` AFTER INSERT ON `bestelregel` FOR EACH ROW BEGIN if Product.productnr = NEW.productnr then Update Product Set Hoeveelheid = (Hoeveelheid - NEW.aantal) Where productnr = NEW.productnr; end if; END

i have written a trigger in mysqlworkbench that should update the product stock after a new line had been added to the order table(my code is in dutch so thats why im explaining this) But it doesn't work, when i try to apply it mysqlworkbench says its sql code contains errors.

Here's the trigger:

CREATE DEFINER = CURRENT_USER TRIGGER `winkel`.`bestelregel_AFTER_INSERT` AFTER INSERT ON `bestelregel` FOR EACH ROW BEGIN if Product.productnr = NEW.productnr then Update Product Set Hoeveelheid = (Hoeveelheid - NEW.aantal) Where productnr = NEW.productnr; end if; END

最满意答案

if语句中的Product是什么？ 毫无疑问，这会产生语法错误，因为它无法识别。

我认为你打算让身体简单：

Update Product p. Set Hoeveelheid = (Hoeveelheid - NEW.aantal) Where p.productnr = NEW.productnr;

if无关紧要。

我希望完整版看起来像：

DELIMITER $$ CREATE DEFINER = CURRENT_USER TRIGGER `winkel`.`bestelregel_AFTER_INSERT` AFTER INSERT ON `bestelregel` FOR EACH ROW BEGIN UPDATE Product p SET Hoeveelheid = (Hoeveelheid - NEW.aantal) WHERE p.productnr = NEW.productnr; END $$ DELIMITER ;

What is Product in the if statement? That is, no doubt, generating a syntax error, because it is unrecognized.

I think you intend for the body to be simply:

Update Product p. Set Hoeveelheid = (Hoeveelheid - NEW.aantal) Where p.productnr = NEW.productnr;

The if irrelevant.

I would expect the full version to look something like:

DELIMITER $$ CREATE DEFINER = CURRENT_USER TRIGGER `winkel`.`bestelregel_AFTER_INSERT` AFTER INSERT ON `bestelregel` FOR EACH ROW BEGIN UPDATE Product p SET Hoeveelheid = (Hoeveelheid - NEW.aantal) WHERE p.productnr = NEW.productnr; END $$ DELIMITER ;