creating trigger across different databases

Question

Hi, Is there any way to create triggers on different databases? my requirement is like:- database: a1.db consist table: t1 database:a2.db consist table: t2

now i have to use trigger on t1 (whenever any delete and update operation) happens on t1 a value has to be inserted into t2.

waiting for your feedback...

Answer 1

What are the other databases you are using besides mysql? If Oracle is one of them, then you can create dblinks from Oracle to the other databases, and your trigger (running on Oracle) could use those dblinks to update the tables in the other databases.

You can refer to this link for info on creating dblinks in Oracle: http://download.oracle.com/docs/cd/B12037%5F01/server.101/b10759/statements%5F5005.htm

Here is a link specific for dblinks from Oracle to mysql: http://www.dba-oracle.com/t%5Fhow%5Fcreate%5Fdatabase%5Flink.htm

Answer 2

I can only speak for MySQL, but you should be able to do something like:

CREATE TRIGGER ad_t1 AFTER DELETE ON `a1.db`.t1
FOR EACH ROW
INSERT INTO `a2.db`.t2 VALUES (...)

Answer 3

Looks like you need the MySQL equivalent of link servers (MSSQL) or dblink (Oracle). There is something called the FEDERATE storage engine:

Check here