tb_tickets

tb_sites_21

我正在创建一个触发器

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT ON `tb_tickets` 
       FOR EACH ROW UPDATE tb_sites_21 
       SET color_status = NEW.status 
       WHERE site_id = NEW.site_id;

它工作正常，我唯一需要的是 tb_sites_21，我希望从创建新条目的 tb_tickets 的 program_id 中挑选这个 21。

像这样:

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT ON `tb_tickets` 
       FOR EACH ROW UPDATE tb_sites_NEW.program_id 
       SET color_status = NEW.status 
       WHERE site_id = NEW.site_id;

或

 CREATE DEFINER=root@localhost 
         TRIGGER color_changed AFTER INSERT ON tb_tickets FOR EACH ROW 
         SET @table_name := (SELECT CONCAT("tb_sites_" , program_id) 
         FROM tb_tickets 
         WHERE ticket_id = NEW.ticket_id); 
         UPDATE table_name set 
         color_status = NEW.status WHERE site_id = NEW.site_id

我如何实现这一点？

最佳答案

您不能使用变量作为表名。这是关于 SQL 的一个事实，无论您走到哪里，查询以及查询中引用的所有表和列都必须在解析查询时固定。对于触发器，这意味着表名必须在您 CREATE TRIGGER 时固定。

另一种方法是将动态 SQL 与 PREPARE and EXECUTE 一起使用.这允许您从字符串表达式构建查询，并让 MySQL 在运行时解析和执行它。

它可能看起来像这样:

SELECT CONCAT("tb_sites_" , program_id) INTO @table_name
     FROM tb_tickets WHERE ticket_id = NEW.ticket_id; 
SET @sql = CONCAT('UPDATE ', @table_name, 
    ' SET color_status = ? WHERE site_id = ?');
SET @color_status = NEW.status, @site_id = NEW.site_id;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @color_status, @site_id;
DEALLOCATE PREPARE stmt;

不幸的是，这在触发器中不起作用。

https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html说:

SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers.

P.Salmon 和 Lukasz Szozda 的其他评论和回答一直试图解释这一点，但您似乎没有在听。

三种选择:

<强>1。对每个案例进行硬编码(Lukasz Szozda 的回答)

我会使用 CASE 语句而不是 IF/THEN/ELSE IF block 链，但逻辑是相同的。你需要

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT
BEGIN
 CASE NEW.program_id
 WHEN 1 THEN
   UPDATE tb_sites_1 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 2 THEN
   UPDATE tb_sites_2 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 3 THEN
   UPDATE tb_sites_3
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ...etc...
 END
END

这有一个缺点，如果你有很多表要更新，它会变得很长，并且每次添加新表时都需要重新定义触发器。

<强>2。使用存储过程而不是触发器(Rick James 的回答)

这不使用触发器。相反，它运行两个语句，一个 INSERT，然后是相应站点表的 UPDATE。您可以使用 PREPARE/EXECUTE 语法在存储过程中执行此操作。

CREATE PROCEDURE InsertTicket(
  IN in_ticket_id INT,
  IN in_program_id INT, 
  IN in_color_status VARCHAR(10),
  IN in_site_id INT)
BEGIN
  DECLARE table_name VARCHAR(64);

  -- First insert into the tickets table
  INSERT INTO tb_tickets 
    SET ticket_id = in_ticket_id,
        program_id = in_program_id, 
        color_status = in_color_status,
        site_id = in_site_id;

  -- Second, do a dynamic update into the respective site table
  SET table_name = CONCAT('tb_sites_', in_program_id);
  SET @sql = CONCAT('UPDATE ', table_name, 
    ' SET color_status = ? WHERE site_id = ?');
  SET @color_status = in_color_status, @site_id = in_site_id;
  PREPARE stmt FROM @sql;
  EXECUTE stmt USING @color_status, @site_id;
  DEALLOCATE PREPARE stmt;
END

您还可以在任何应用程序编码语言中执行一对等价的语句。您不需要执行存储过程。

您可以使用事务来包装这两个语句，以便它们同时提交，否则都回滚。

此替代方案将要求您更改应用程序插入票证的方式。所以你必须改变应用程序代码。如果您无权更改应用程序代码，这不是一个好的选择。

<强>3。将站点表重构为一个表(Paul Spiegel 的评论)

有几个人提出了这个建议，但您说您无权更改表格设计。这很不幸，但很常见。更改应用程序中的表设计代价高昂，因为可能有很多应用程序代码取决于当前的表设计。所有代码都需要重构以支持对表的更改。

关于mysql - 在触发器中获取动态表名，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/56898451/