Cross database trigger in Mysql

Question

Is it possible to apply trigger for cross database access in MySQL If so please give one example. My purpose is to insert/update/delete data in database2 if there is any new data inserted/updated/deleted in database1. I am using MySQL 5.1

Cross database (different database installations) triggers do not exist. (cross schema, on one server, offcourse do exist). Try replication, or MySQLDump. http://stackoverflow.com/questions/4406808/importing-mysql-database-from-one-server-to-another — Konerak, Sep 14 '12 at 09:36
@Devart Actually I have a database which gets populated dynamically from my CMS and with each insert or update I need my another database to have same insert or update without writing code and using trigger. — mridul4c, Sep 14 '12 at 12:13
You can synchronize databases with a help of [Date Comparison tool](http://www.devart.com/dbforge/mysql/studio/database-synchronization.html) in dbForge Studio for MySQL. You can run it in command line mode. — Devart, Sep 14 '12 at 12:22

score 5 · Answer 1 · answered Mar 23 '18 at 14:35

I know it is an old topic but I just had to implement a cross-database trigger myself and I would like to show the solution here as other readers might benefit from it.

Inside the code of the trigger it is possible to refer to the target database with its full name database_name.table_name.

Suppose you have two databases on the same server: database1 and database2. In each of the two databases you have the following table:

CREATE TABLE 'test' (
  'id' int(10) DEFAULT NULL,
  'name' varchar(100) DEFAULT NULL
)

In the database1 you create the following INSERT trigger:

USE database1;

DELIMITER //
CREATE TRIGGER sync_insert AFTER INSERT ON test 
FOR EACH ROW
BEGIN
INSERT INTO database2.test SET id = NEW.id, name=NEW.name;
END; //
DELIMITER ;

Define similar triggers for UPDATE and DELETE.

I'd say this solution is more simple and straightforward than using procedures.

I tested the above on MySQL 5.1.73 and MariaDB 10.2.13.

score 4 · Accepted Answer · edited Jan 11 '19 at 03:37

Yes, you can. You could make a procedure and call it in your trigger. Procedure example :

DELIMITER //

CREATE PROCEDURE delete(in table VARCHAR(300), in db VARCHAR(300), in id INT)
BEGIN

set @query0 = CONCAT('DELETE FROM ', new_db, '.', tabela, ' WHERE id=',id);

PREPARE select_query0 FROM @query0;
EXECUTE select_query0;
DEALLOCATE PREPARE select_query0;

END; //

DELIMITER ;

And then to create the trigger:

CREATE TRIGGER del_trigger BEFORE DELETE ON table
  FOR EACH ROW BEGIN
    CALL delete(db, table, OLD.id); 
  END;

Cross database trigger in Mysql

2 Answers2

Linked