Fetch dynamic table name in trigger

Question

tb_tickets

tb_sites_21

I am creating a trigger

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;

it is working fine only thing I need is tb_sites_21, I want this 21 to be picked from program_id of tb_tickets for which new entry is made.

Something like this:

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;

or

 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

How do I achieve this ?

Answer 1

You can't use a variable for a table name. This is a fact about SQL wherever you go, that the query and all tables and columns referenced in the query must be fixed at the time the query is parsed. In the case of a trigger, that means the table name must be fixed at the time you CREATE TRIGGER.

The alternative is to use dynamic SQL with PREPARE and EXECUTE. This allows you to build a query from a string expression, and make MySQL parse and execute it at runtime.

It might look something like this:

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;

Unfortunately, this doesn't work in a trigger.

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

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

Other comments and answers from P.Salmon and Lukasz Szozda have been trying to explain this, but you don't seem to be listening.

There are three alternatives:

1. Hard-code each case (Lukasz Szozda's answer)

I would have used the CASE statement instead of a chain of IF/THEN/ELSE IF blocks, but the logic is the same. You need

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

This has a disadvantage that it can get long if you have many tables to update, and you need to redefine the trigger each time you add a new table.

2. Use a stored procedure instead of a trigger (Rick James' answer)

This does not use a trigger. Instead, it runs two statements, an INSERT followed by an UPDATE of the respective site table. You can do this in a stored procedure using the PREPARE/EXECUTE syntax.

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

You can also do an equivalent pair of statements in any application coding language. You don't need to do a stored procedure.

You can use a transaction to wrap the two statements, so they are both committed simultaneously, or else both rolled back.

This alternative will require you to change the way the application inserts tickets. So you must change application code. If you don't have permission to change application code, this isn't a good alternative.

3. Refactor the sites table into one table (Paul Spiegel's comment)

Several people suggested this, but you said you don't have permission to change the table designs. This is unfortunate, but very common. It's expensive to change the table design in an application, because there may be lots of application code depending on the current table design. All the code needs to be refactored to support a change to the tables.

Answer 2

Table partitioning per program_id is a tricky design: SELECT * FROM sales + @yymm

---

You cannot dynamic SQL/prepared statements inside trigger so the only vialble option is using IF statement but you need to know the table names in advance.

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;

Answer 3

As with many questions on the Forum, I will try to take a step back and discover what the real issue is; then tackle it.

The apparent problem is that you have decided to use a TRIGGER, but hit a brick wall due to two things: (1) the schema has multiple identical tables, and (2) Triggers don't allow "prepare".

Backing up, the real problem is to store some data based on "program_id".

So, if we back off on using a Trigger (2), we could go with a Stored Procedure and/or application code to do the task. This goes along with something I often say: "Triggers (and Foreign Keys) have limitations; you can't necessarily do everything you would like to do with them."

But, I see (1) as the more serious design flaw. It is almost always 'improper schema design' to have multiple identical tables, where they differ only by the table name. It is usually much easier, and more efficient in the long run, to have an extra column (program_id) in a single table.

I understand that there are exceptions to the 'single table' approach. If you feel that you must have separate tables, please elaborate so we can discuss further.