错误#1054-“新建"中的未知列"program_id" [英] ERROR #1054 - Unknown column 'program_id' in 'NEW

问题描述

正在努力实现-

我正在尝试更新tb_sites_3中的color_status(3将根据从tb_tickets获得的program_id是动态的)，只要在tb_jobs上进行了任何插入.

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.

错误

在创建触发器时，出现以下错误错误#1054-新建"中的未知列"program_id"

While creating a trigger I am getting the following error ERROR #1054 - Unknown column 'program_id' in 'NEW'

tb_tickets

tb_jobs

tb_sites_3

DELIMITER //
    CREATE TRIGGER trig_job_color
           BEFORE INSERT ON `tb_jobs`
           FOR EACH ROW 
    BEGIN
    SET NEW.program_id = (Select program_id from tb_tickets
    where tb_tickets.job_id = NEW.job_id);
    SET NEW.status = (Select status from tb_tickets
    where tb_tickets.job_id = NEW.job_id);

     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;
     END CASE;
    END //
    DELIMITER ;

表格定义

tb_tickets

CREATE TABLE `tb_tickets` (
 `id` int(15) NOT NULL,
 `ticket_id` int(15) NOT NULL,
 `job_id` int(11) NOT NULL,
 `site_id` varchar(200) NOT NULL,
 `program_id` int(11) NOT NULL,
 `status` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

tb_jobs

CREATE TABLE `tb_jobs` (
 `job_id` int(11) NOT NULL AUTO_INCREMENT,
 `job_creation` date DEFAULT NULL,
 PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

tb_sites_3

CREATE TABLE `tb_sites_3` (
 `id` int(15) NOT NULL AUTO_INCREMENT,
 `color_status` int(15) NOT NULL,
 `site_id` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

推荐答案

创建触发器 trig_job_color 后插入 tb_jobs 对于每个行开始SET @program_id =(从tb_tickets中选择program_id其中tb_tickets.job_id = NEW.job_id);

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

这篇关于错误#1054-“新建"中的未知列"program_id"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！