CODEWITHSUNDEEP
mysqltriggersautomation
alamirfan
alamirfan

Reputation: 523

SQL : How to update table Automatically in 2nd database when table in 1st database is updated?

I have database db1 and db2 having the table mytable on both the databases in same server.

Both the table contains exactly the same columns.

For Ex:

SNo  |  fname  |   lname  | Mobile | Status

Both the table has a column name Status

What should I do so that when value of column Status is updated in table mytable in database db1 then value of column Status is updated AUTOMATICALLY in table mytable in database db2

I don't know what actually it is called. Perhaps Trigger !

Upvotes: 2

Views: 1991

Answers (1)

backtrack
backtrack

Reputation: 8154

You have to create a trigger in Mysql.

Example:

 CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;

I am using the above trigger to update two tables.

Have a look on this tutorial

update

You can not trigger on a particular column update in SQL, it is applied on a row.

You can put your condition for column inside your trigger like below

DELIMITER $$
CREATE  TRIGGER  myTrigger AFTER UPDATE ON db1.mytable
FOR EACH ROW
BEGIN

if NEW.Status <> OLD.Status
then

update db2.mytable set Status = NEW.Status where sno = OLD.sno;

END if;

END $$

Upvotes: 4

Related Questions