MySQL Trigger After Insert, Action JOIN 2 tables

Question

I need to create a trigger (after insert on one table) on MySQL, but the action needs to join 2 tables, for inserting into a third table. My script below returns no error, but the row is not inserted into the third table.

The first table (on which the after-insert trigger should work):

Z_TAXO      
ID  term_ID taxo_name
1    1      dept
2    2      staff
3    4      course
4    5      dept

The second table to be joined in the trigger:

Z_TERM  
term_ID name
1       Engineering
2       Andy
4       Metallurgy
5       Business
6       Arts

The third table. If the Z_TAXO table has a new row with taxo_name = "dept", the row (joined with table Z_TERM) needs to be inserted into this table:

Z_DEPTS 
ID  dept_name
1   Engineering
4   Business

I created a trigger:

delimiter //
    CREATE TRIGGER TRG_NEW_DEPT
    AFTER INSERT ON Z_TAXO
    FOR EACH ROW
    BEGIN
        DECLARE _dept_ID bigint(20);
        DECLARE _dept_name varchar(200);
        IF Z_TAXO.taxo_name = "DEPT" THEN
            BEGIN
                SELECT Z_TAXO.ID INTO _dept_ID FROM Z_TAXO, Z_TERM
                WHERE Z_TAXO.ID = new.Z_TAXO.ID AND Z_TAXO.term_ID = Z_TERM.term_ID;
                SELECT Z_TERM.name INTO _dept_name FROM Z_TERM, Z_TAXO
                WHERE Z_TAXO.term_ID = Z_TERM.term_ID AND Z_TAXO.ID = new.Z_TAXO.ID;
                INSERT INTO Z_DEPTS (ID, dept_name) VALUES (_dept_ID, _dept_name);
            END;
        END IF;
    END//
delimiter ;

Then inserted a row to the Z_TAXO table:

INSERT INTO Z_TAXO (ID, term_ID, taxo_name) VALUES (5, 6, "dept");

Expecting to have this new row in table Z_DEPTS:

ID  dept_name
5   Arts

But when I select * from Z_DEPTS, the result is still:

ID  dept_name
1   Engineering
4   Business

What can be wrong? I can't modify the design of the tables, because they came from a wordpress Plugin. Thanks in advance!

Thank you for the answer. I tried to change it, new trigger succesfully created, but encountered error "column Z_TAXO.ID not known in the WHERE clause", when INSERT-ing the new row. I'll check the clause. — UL Paris
Revised the trigger, error when executed: "Error Code: 1054. Unknown column 'new.Z_TAXO.ID' in 'field list'" — UL Paris

P.Salmon P.Salmon · Accepted Answer · 2018-02-13T11:50:24

Couple of comments about your code. 1) When using new. qualifiers you don't further qualify with the table name so new.z_taxo.id is invalid andd should simply be new.id 2) You don't need a begin..end block in a mysql if statement 3) if just doesn't make sense referring to the table z_taxo in your select stataments - a simple insert select will do.

try

drop trigger if exists trg_new_dept;
delimiter //
    CREATE TRIGGER TRG_NEW_DEPT
    AFTER INSERT ON Z_TAXO
    FOR EACH ROW
    BEGIN
            INSERT INTO Z_DEPTS (ID, dept_name) 
            select term_id, name
            from z_term 
            where   term_id = new.term_id;
     END//
delimiter ;

MySQL Trigger After Insert, Action JOIN 2 tables

1 Answers