我试图在插入表之前执行触发器.

表结构： –

服务： – 身份证,姓名.

诊所： – id,company_id,name,service_id.

user： – id,clinic_id,company_id,service_id.

CREATE TRIGGER user_before_insert_trigger BEFORE INSERT ON `user` FOR EACH ROW
      BEGIN                            
         SET NEW.service_id= (select CL.service_id from clinic as CL where CL.id=NEW.clinic_id LIMIT 1);    

         SET NEW.company_id= (select CL.company_id from clinic as CL where CL.id=NEW.clinic_id LIMIT 1);         
     END

我需要使用trigger在insert上设置service_id和company_id.

我已经执行了单独的查询以从临床表中获取service_id和company_id.这需要在单个查询中进行转换.

请提出一些改进查询的建议.

解决方法:

您需要声明局部变量并将值分配给选择查询Mysql Select Into

CREATE TRIGGER user_before_insert_trigger BEFORE INSERT ON `user` FOR EACH ROW
BEGIN
    DECLARE service_id integer; 
    DECLARE company_id integer; 
    SELECT CL.service_id,CL.company_id into service_id,company_id from clinic as CL where CL.id=NEW.clinic_id LIMIT 1
    SET NEW.service_id= service_id;
    SET NEW.company_id= company_id;         
END