How to set trigger if insert on one table update same column on other table

Question

tableONE

tb1_id   tbl_name    tb2_id
1        ab           0

tableTOW

insert into tableTOW("tb2_id, tb2_name,tb1_id) value('10,'name',1)

the result will be:

tb2_id     tb2name    tb1_id
10          name        1

Now the tableONE should be update the column tb2_id = 12 because on insert to tableTOW where the tb1_id was (1)

how i can set such trigger during insert update other table with where condition(WHERE tableTOW.tb1_id = tableONE.tb1_id)..

regards

`column tb2_id = 12` or `column tb2_id = 10` since you are entering 10 in the table2 for `tb2_id` and 1 for `tb1_id` — Abhik Chakraborty, May 10 '14 at 13:08

score 1 · Answer 1 · edited May 23 '17 at 11:50

Use Procedures to make it atomic otherwise do it in your application logic

# change the delimiter to $$, so you can use semicolon in create procedure
DELIMITER $$

USE database_name$$

DROP PROCEDURE IF EXISTS data_insert$$

CREATE PROCEDURE data_insert(IN tb1_id INT, IN tbl_name VARCHAR(50), IN tb2_id INT)
BEGIN

INSERT INTO tableONE (tb1_id, tbl_name, tb2_id) VALUES (tb1_id, tbl_name, tb2_id);

 ######## ######## ######## ######## #######    
####### WRITE YOUR UPDATE QUERY HERE ########
 ######## ######## ######## ######## #######

END$$
# change the delimiter back to semicolon
DELIMITER ;

Procedure code taken from insert into in stored procedure with parameters MYSQL doesnt work

score 0 · Answer 2 · answered May 11 '14 at 13:39

CREATE TRIGGER `ins_trig` AFTER INSERT ON `tableTOW`
FOR EACH ROW UPDATE tableONE
     SET tb2_id= new.tb2_id
   WHERE tb1_id = NEW.tb1_id

and for update i used

CREATE TRIGGER `ins_trig` AFTER INSERT ON `tableTOW`
FOR EACH ROW UPDATE tableONE
     UPDATE tb2_id= new.tb2_id
     WHERE tb1_id = NEW.tb1_id

thanks for comment and answers..

How to set trigger if insert on one table update same column on other table

2 Answers2