在 MySQL 触发器中，是否可以使用 NEW.col 设置用户变量并在更新查询中使用它? [英] In MySQL trigger, is it possible to set a user variable with NEW.col and use that in update query?

问题描述

在此先感谢您的帮助.我四处看了看，找不到一个很像我需要帮助的例子.我正在创建一个触发器来在插入后更新表，但在插入发生之前我不知道要更新的表名.这是我尝试使用的代码，但出现错误.

Thanks in advance for the help. I looked all over and couldn't find an example quite like what I'm needing help with. I'm creating a trigger to update a table after insert but I don't know the table name to update until after the insert happens. This is the code I'm trying to use, but I get an error.

BEGIN
  SET @ven = NEW.`ven_code`;
  SET @ventable = concat('pp_ven_',@ven);
  UPDATE @ventable SET `stock_qty`=NEW.`endingStock` WHERE `iin`=NEW.`iin`;
END

推荐答案

这在动态 sql/Prepared Statement 中是不可能的.它会生成错误代码:1336.在尝试甚至CREATE TRIGGER时，存储函数或触发器中不允许使用动态SQL.

This is not possible as described with dynamical sql / Prepared Statement. It would generate Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger upon the attempt to even CREATE TRIGGER.

最接近自动化的是使用 CREATE EVENT.事件是按您选择的时间表/间隔运行的预定存储程序.间隔是:

About the closest you could get to automation is to use CREATE EVENT. Events are scheduled stored programs that run on the schedule / Interval of your choosing. The intervals are:

间隔:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
          WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
          DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

您可以在一行上设置一个标志"，例如上面描述的具有 After Insert 触发器的表.然后事件可以执行Prepared Statement 动态并执行它.

Your could set a "flag" so to speak on a row, such as the table your are depicting above that has the After Insert trigger. The event could then perform the Prepared Statement dynamically and execute it.

在事件管理上查看我的回答.

我不得不说，即使在活动中运行，您所提议的内容几乎总是表明架构设计不佳，无法很好地接受同行评审.

I have to say that even if run in an event, what you are proposing is almost Always the sign of a poor schema design that wouldn't hold up well to peer review.

动态 sql 和 Prepared Stmt 被禁止的一个原因是因为触发器需要快速，甚至 DDL 可能会被潜入字符串并执行.触发器中不允许使用像 ALTER TABLE 这样的 DDL stmts(它们可能需要数小时才能真正运行).

A reason why the dynamic sql and a Prepared Stmt is disallowed is because the trigger needs to be fast, and even DDL could be snuck into the string and executed. And DDL stmts like ALTER TABLE are disallowed in triggers(they could take hours to run literally).

您的架构也可以共享一个表，其中一列 ven_code 作为微分列.相反，您选择为每个 ven_code 创建新表.这通常是一个糟糕的设计和性能选择.

Your schema could just as well have one table shared with a column ven_code being the differentiator column. Instead you chose to create new tables for each ven_code. That typically is a poor design and performance choice.

如果您需要架构设计方面的帮助，我很乐意在聊天室与您讨论.

If you need help with schema design, I am happy to chat about it with you in a chat room.

这篇关于在 MySQL 触发器中，是否可以使用 NEW.col 设置用户变量并在更新查询中使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！