#StackBounty: #mysql #sql #triggers Fetch dynamic table name in trigger

Bounty: 100

tb_tickets
enter image description here

tb_sites_21

enter image description here

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 ?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.