Cross database trigger in Mysql

问题:

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

回答1:

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;

回答2:

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.