CODEWITHSUNDEEP
mysql
user1638466
user1638466

Reputation: 310

MySQL trigger alter second table on a specific row

I created a trigger like:

CREATE TRIGGER `update_tb2` AFTER UPDATE ON  `table_1` FOR EACH ROW 

IF new.parameter =  'param1' 
    THEN UPDATE table_2 SET table_2.input =  '10' WHERE table_2.key =  '0';    
END IF ;

It works as expected, when I update any row in the table_1 the one with key=0 in the other table is updated. Since there is a field key in both tables, I want to update in the second table to row who has the same key of the updated row in table_1. I can't figure out how to use table_2.key inside the IF block.

Upvotes: 1

Views: 73

Answers (2)

Gustavo Vargas
Gustavo Vargas

Reputation: 2597

try this:

CREATE TRIGGER `update_tb2` AFTER UPDATE ON  `table_1` FOR EACH ROW 

IF new.parameter =  'param1' 
    THEN UPDATE table_2 SET table_2.input =  '10' WHERE table_2.key =  old.key;
END IF ;

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

You can use the new or old keyword (in case you change the key value, you may want to use one or the other). It let's you access all the values from the record that was updated on table_1:

CREATE TRIGGER `update_tb2` AFTER UPDATE ON  `table_1` FOR EACH ROW 

IF new.parameter =  'param1' 
    THEN UPDATE table_2 SET table_2.input =  '10' WHERE table_2.key =  new.key;    
END IF ;

You can see an example on this fiddle

Upvotes: 1

Related Questions