0

I have two MySQL databases the first one has one table t1 that has three fields id, t2id, f and the second one has table t2 that has two fields id, f the t1.t2id should have the same value as t2.id where t1.f = t2.f, however the value of t2.id is changing every day.

What I want to achieve is that whenever the value of t2.id is changed the value of t1.t2id will be updated as well, is that possible using a procedure?

Any other suggestions?

Naveed Ramzan
  • 3,565
  • 3
  • 25
  • 30
m.alsioufi
  • 5
  • 4
  • You need to use a [**Trigger**](https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html) after the INSERT or the UPDATE on the table `t2`. – Hamza Abdaoui Mar 14 '18 at 16:16

1 Answers1

0

As was commented, you can use TRIGGERs (docs).

You would probably want two triggers, one upon insertion and one upon update.

DELIMITER //
CREATE TRIGGER insert_t1 AFTER INSERT ON t2
   FOR EACH ROW
   BEGIN
       INSERT INTO t1
       (t2id, f)
       VALUES
       (NEW.id, NEW.f);
   END //

   CREATE TRIGGER insert_t1 AFTER UPDATE ON t2
   FOR EACH ROW
   BEGIN
       UPDATE t1
       SET 
           f = NEW.f
       WHERE 
           t2id = NEW.id;
   END //
DELIMITER ;

Edit: To adjust for the multiple databases, you need to qualify them, assuming the user has permissions to both databases (I'm assuming they're on the same host).

So rather than: CREATE TRIGGER insert_t1 AFTER INSERT ON t2.... you would do CREATE TRIGGER db1.insert_t1 AFTER INSERT ON db2.t2...

kchason
  • 2,836
  • 19
  • 25
  • Thank you for your answer, but as I mentioned in my question the two tables are related to two different databases so would the triggers work in this case? – m.alsioufi Mar 14 '18 at 17:28
  • Updated my answer, qualification seems to be supported in MySQL: https://stackoverflow.com/questions/1832759/creating-trigger-across-different-databases – kchason Mar 15 '18 at 10:27