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
4
-
Cross database (different database installations) triggers do not exist. (cross schema, on one server, offcourse do exist). Try replication, or MySQLDump. stackoverflow.com/questions/4406808/…Konerak– Konerak2012-09-14 09:36:12 +00:00Commented Sep 14, 2012 at 9:36
-
@mridul4c What actually are you going to do?Devart– Devart2012-09-14 09:48:56 +00:00Commented Sep 14, 2012 at 9:48
-
@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– mridul4c2012-09-14 12:13:17 +00:00Commented Sep 14, 2012 at 12:13
-
You can synchronize databases with a help of Date Comparison tool in dbForge Studio for MySQL. You can run it in command line mode.Devart– Devart2012-09-14 12:22:40 +00:00Commented Sep 14, 2012 at 12:22
Add a comment
|
2 Answers
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.
Comments
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;
