Using Trigger to update table in another database

Question

im using the following trigger to update the user table in another database in mysql 5.0.7 The creation of trigger gives no error but upon updating the user table in the first database the trigger is not working. Any suggestions?

DELIMITER $$         
DROP TRIGGER IF EXISTS after_update_user;

CREATE TRIGGER after_update_user;

AFTER UPDATE ON db_test.user  FOR EACH ROW;
BEGIN
    UPDATE TABLE db_testplus.user;
    SET  name = NEW.name;

    WHERE id = NEW.id;
END

$$
DELIMITER ;

I also used this code without the semicolons but still the same

DELIMITER $$         
DROP TRIGGER IF EXISTS after_update_user

CREATE TRIGGER after_update_user

AFTER UPDATE ON db_test.user  FOR EACH ROW
BEGIN
    UPDATE TABLE db_testplus.user
    SET  name = NEW.name

    WHERE id = NEW.id
END;

$$
DELIMITER ;

Finally the code that worked

delimiter |
DROP TRIGGER IF EXISTS after_update_user|
 CREATE TRIGGER after_update_user AFTER UPDATE ON db_test.user
  FOR EACH ROW BEGIN
     UPDATE db_testplus.user SET name = NEW.name WHERE id = NEW.id;
  END;
|
delimiter ;

The table in the second database is not updating after update in the first table — Abhishek, Jan 18 '12 at 13:11
In one post there was this suggestion to put semicolons after every statement so I tried with this — Abhishek, Jan 18 '12 at 13:15

score 2 · Answer 1 · answered Jan 18 '12 at 13:15

2

Could you please check below

AFTER UPDATE ON db_test.user  FOR EACH ROW
BEGIN
    UPDATE TABLE db_testplus.user
    SET  name = NEW.name

    WHERE id = NEW.id
END;

answered Jan 18 '12 at 13:15

Fahim Parkar

30,974
45
160
276

did you used `;` after `END` or after `WHERE id = NEW.id`.. I am asking as your update says you are using after `WHERE id = NEW.id`... Try with adding `;` after END as I have shown... – Fahim Parkar Jan 18 '12 at 13:24
Does it matter what Storage Engine is being used. Im using MyISAM – Abhishek Jan 18 '12 at 13:30
not sure, let me check on google... I am not much in MYSQL but I share that much I know... – Fahim Parkar Jan 18 '12 at 13:34
check if below link helps you http://stackoverflow.com/questions/7507636/oracle-problem-creating-trigger-that-updates-another-table, http://forums.devshed.com/ms-sql-development-95/use-trigger-to-update-data-in-another-db-149985.html, http://stackoverflow.com/questions/1799933/update-data-from-one-table-to-another-in-a-database, http://stackoverflow.com/questions/6765258/how-to-use-a-trigger-to-update-a-column-in-one-table-from-an-insert-on-another-t – Fahim Parkar Jan 18 '12 at 13:51
more links http://stackoverflow.com/questions/6765258/how-to-use-a-trigger-to-update-a-column-in-one-table-from-an-insert-on-another-t – Fahim Parkar Jan 18 '12 at 13:56
Thanks a lot. I found the problem it was about using delimiters after DROP statement. – Abhishek Jan 19 '12 at 05:37

score 2 · Answer 2 · answered Jan 18 '12 at 14:44

2

Try this;

CREATE TRIGGER after_update_user

AFTER UPDATE ON db_test.user  FOR EACH ROW
  UPDATE TABLE db_testplus.user
  SET  name = NEW.name
  WHERE id = NEW.id;

Omitting begin-end keywords worked for me.

answered Jan 18 '12 at 14:44

Korhan Ozturk

11,148
6
36
49

score 0 · Answer 3 · answered Dec 04 '19 at 02:05

0

This works for me in MySQL 5.1.73:

CREATE TRIGGER `after_update`
AFTER UPDATE ON `test_table`
FOR EACH ROW UPDATE another_db.updated_table
SET  some_name = NEW.some_name
WHERE id = NEW.id

answered Dec 04 '19 at 02:05

IlludiumPu36

4,196
10
61
100

Using Trigger to update table in another database

3 Answers3

Linked