ERROR #1054 - Unknown Column 'program_id' In 'NEW

Tring to achieve- I am trying to update color_status in tb_sites_3 (3 will be dynamic based on program_id which we will get from tb_tickets) Whenever any insert is made on tb_jobs

Solution 1:

CREATE TRIGGER trig_job_color AFTER INSERT ON tb_jobs FOR EACH ROW BEGIN SET @program_id = (Select program_id from tb_tickets where tb_tickets.job_id = NEW.job_id);

SET @newstatus = (Select status from tb_tickets
where tb_tickets.job_id = NEW.job_id);
SET @newsite_id = (Select site_id from tb_tickets
where tb_tickets.job_id = NEW.job_id);

CASE @program_id
 WHEN 1 THEN
   UPDATE tb_sites_3 
   SET tb_sites_3.color_status = @newstatus 
   WHERE tb_sites_3.site_id = @newsite_id;
 WHEN 2 THEN
   UPDATE tb_sites_3 
   SET tb_sites_3.color_status = @newstatus 
   WHERE tb_sites_3.site_id = @newsite_id;
 WHEN 3 THEN
   UPDATE tb_sites_3
   SET tb_sites_3.color_status = @newstatus 
   WHERE tb_sites_3.site_id = @newsite_id;
 END CASE;
 END

Solution 2:

This worked for me using variables.

DELIMITER //
    CREATE TRIGGER trig_job_color
           AFTER INSERT ON `tb_jobs`
           FOR EACH ROW 
    BEGIN
    DECLARE x, y INT DEFAULT 0;
    DECLARE z varchar(50);
    SET x = (Select program_id from tb_tickets
    where tb_tickets.job_id = NEW.job_id);
    SET y = (Select status from tb_tickets
    where tb_tickets.job_id = NEW.job_id);
    SET Z = (Select site_id from tb_tickets
    where tb_tickets.job_id = NEW.job_id);
     CASE x
     WHEN 1 THEN
       UPDATE tb_sites_1 
       SET color_status = y
       WHERE site_id = z;
     WHEN 2 THEN
       UPDATE tb_sites_2 
       SET color_status = y
       WHERE site_id = z;
     WHEN 3 THEN
       UPDATE tb_sites_3
       SET color_status = y 
       WHERE site_id = z;
     END CASE;
    END //
    DELIMITER ;