MYSQL - 在表中使用 AFTER UPDATE 进行内部连接 [英] MYSQL - Inner Join with AFTER UPDATE in table

问题描述

我的 mySQL 数据库中有一个名为 jobPosts 的表.一旦我当前的 jobPost 表中发生更新，我就会尝试将数据插入另一个表 (jobRequest).

I have a table called jobPosts in my mySQL datbase. I am trying to to insert data into another table (jobRequest) once an update has occurred in my current jobPost table.

这是我的 SQL 表 &查询.

Here is my SQL tables & queries.

           DELIMITER //
           CREATE DEFINER=`root`@`localhost` TRIGGER 
           trg_jobPost_after_update
           AFTER UPDATE ON jobPost
           FOR EACH ROW
           BEGIN
           INSERT INTO jobRequest (userID, name, email, phoneNo, 
            location, jobID, 
                   title, level, dateFrom, dateTo, description, 
                   jobStatus, emailTeacher, nameTeacher, 
                   locationTeacher, phoneNoTeacher, cv, gardavetting, 
                   linkedin) 
      SELECT 
        jobPost.userID
      ,  jobPost.name
      ,  jobPost.email
      ,  jobPost.phoneNo
      ,  jobPost.location
      ,  jobPost.jobID
      ,  jobPost.title
      ,  jobPost.level
      ,  jobPost.dateFrom
      ,  jobPost.dateTo
      ,  jobPost.description
      , users.nameTeacher
      , users.locationTeacher
      , users.phoneNoTeacher
      , users.cv
      , users.gardavetting
      , users.linkedin
      , NEW.jobStatus
     , NEW.emailTeacher
   FROM jobPost
   WHERE jobPosts.emailTeacher = NEW.emailTeacher;
  END //
  DELIMITER ;

表格 -

jobPost 表

用户表

工作请求表

推荐答案

从评论来看，您似乎只需要 jobPost 和 之间的 Inner JoinemailTeacher 上的 users 表.

From comments, it seems that you simply need a Inner Join between the jobPost and users table, on emailTeacher.

此外，与 Insert 子句相比，Select 中的某些列丢失了；我已按正确顺序添加它们.

Also, some columns from Select were missing, in comparison to Insert clause; I have added them in proper order.

DELIMITER //
CREATE DEFINER=`root`@`localhost` TRIGGER 
    trg_jobPost_after_update
    AFTER UPDATE ON jobPost
       FOR EACH ROW
       BEGIN

       -- Check if jobStatus is Updated (then only we Insert) 
       IF ( OLD.jobStatus <> NEW.jobStatus AND 
            OLD.emailTeacher <> NEW.emailTeacher ) THEN 
         INSERT INTO jobRequest (userID, name, email, phoneNo, 
                                 location, jobID, title, level, 
                                 dateFrom, dateTo, description, 
                                 jobStatus, emailTeacher, nameTeacher, 
                                 locationTeacher, phoneNoTeacher, cv, 
                                 gardavetting, linkedin) 
         SELECT 
           jobPost.userID
        ,  jobPost.name
        ,  jobPost.email
        ,  jobPost.phoneNo
        ,  jobPost.location
        ,  jobPost.jobID
        ,  jobPost.title
        ,  jobPost.level
        ,  jobPost.dateFrom
        ,  jobPost.dateTo
        ,  jobPost.description
        ,  NEW.jobStatus 
        ,  NEW.emailTeacher 
        ,  users.nameTeacher 
        , users.locationTeacher
        , users.phoneNoTeacher
        , users.cv
        , users.gardavetting
        , users.linkedin
        FROM jobPost 
        JOIN users ON users.emailTeacher = jobPost.emailTeacher 
        WHERE jobPost.emailTeacher = NEW.emailTeacher;

      END IF;
END //
DELIMITER ;

这篇关于MYSQL - 在表中使用 AFTER UPDATE 进行内部连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！