CODEWITHSUNDEEP
mysql
user3553131
user3553131

Reputation:

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

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

Upvotes: 0

Views: 43

Answers (2)

user3553131
user3553131

Reputation: 

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..

Upvotes: 0

Rahul Prasad
Rahul Prasad

Reputation: 8222

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

Upvotes: 1

Related Questions