当前位置: 首页 > 知识库问答 >
问题:

在触发器中提取动态表名

长孙昀
2023-03-14

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,我想要这个21是从tb_tickets的program_id中挑选出来的,为它做了新的条目。

类似这样的事情:

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

我如何实现这一点?

共有2个答案

曹经业
2023-03-14

按program_id划分表是一个棘手的设计:选择*FROM sales+@yymm

您不能在触发器内部动态SQL/Prepared语句,因此唯一可行的选项是使用if语句,但您需要事先了解表名。

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT
BEGIN
 IF NEW.program_id = 1 THEN
   UPDATE tb_sites_1 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ELSEIF NEW.program_id = 2 THEN
   UPDATE tb_sites_2 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ELSEIF ... THEN
   -- ...
 ELSE
   -- ...
 END
END;
郎欣然
2023-03-14

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

另一种方法是使用带有PREPARE和Execute的动态SQL。这允许您从字符串表达式构建查询,并使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语法可以在存储过程中使用,但不能在存储函数或触发器中使用。

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

有三种选择:

1.对每个案例进行硬编码(卢卡斯·索兹达的回答)

我会使用case语句,而不是一个IF/then/else IF块链,但逻辑是相同的。你需要

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.将sites表重构为一个表(Paul Spiegel的评论)

有几个人这样建议,但你说你没有更改桌子设计的权限。这很不幸,但很常见。更改应用程序中的表设计是昂贵的,因为可能有大量的应用程序代码取决于当前的表设计。需要重构所有代码以支持对表的更改。

 类似资料:
  • 问题内容: 据我了解,当您调用last_insert_id()时,它是通过连接进行的,因此您将获得插入到调用last_insert_id()的同一连接中的最后一行的ID,对吗? 那么,如果我在“ AFTER INSERT”触发器中调用last_insert_id()怎么办? 我想做的基本上是这样 “ anothertable”中的id与“ sometable”中的id非常重要,这是否可行?还是应该

  • 一点背景: 我对也做了同样的操作--创建了一个将添加到源列表的方法,并且每当更新源列表时,它将使用JavaFX线程上的UI等价类更新目标列表: 因此,现在如果我有更新的并且其项属性更新不是在JavaFX线程上,我就可以轻松地获得一个在JavaFX中显示的列表: 所以现在的问题是: 我的预期是,当显示在中时,只要值发生更改,它就会更新,但预期的行为只持续几秒钟,之后ListView停止更新,这是因为

  • 问题内容: 如何设置Jenkins作业以触发在变量/参数中动态定义的作业? 我的问题的伪代码: 生成操作“触发/调用在其他项目上生成”以及生成后操作“参数化触发器插件”和“生成其他项目”不允许在参数中定义作业名称。 我似乎没有将GroovyAxis插件与Build多重配置项目一起使用。常规代码似乎不在作业构建环境中运行,而是在作业配置保存期间运行。 我了解您可以通过脚本通过其build-start

  • 我设法潜伏在周围并找到一个好的类,它提供用

  • 我想创建ddl触发器(在创建),这将创建一个dml触发器,但我有错误: ORA-06512:8 00604. 00000-"错误发生在递归SQL级别%s"*原因:错误发生在处理递归SQL语句(适用于内部字典表的语句)。*操作:如果可以纠正堆栈上下一个错误中描述的情况,请这样做;否则请联系Oracle支持。

  • userNotesTable: 用户提醒表: 插入触发器: 更新触发器: 这是数据库的当前代码,以及提醒表的特定触发器。我遇到的困难是,从提醒表中的specific中的user notes表中选择特定的名称和额外的内容,所有这些都在更新触发器中。 插入时,和会被插入到提醒和搜索表中,但我希望能够使用特定名称和用户注释表中的额外内容更新搜索表,这可能吗?