web stats
Create Mysql Trigger across Different Databases with Examples | Why Cloud Computing

Create Mysql Trigger across Different Databases with Examples

In one of my previous articles, I wrote about basic mysql trigger with examples. Now, here is an article that’s an extension to it with minor updates. This example can come in really handy if you want to update tables in different databases. And let me tell you, in the examples below, I am using root as my mysql user, so things are smooth for me. You will have to adjust your user permissions if you are doing this with a particular user.

Share our stuff and help us grow. Need help to create something new or fix something old? Contact us to know if we can help 🙂

As usual, I am creating couple of databases (db1 and db2).

mysql> create database db1;

mysql> create database db2;

Now, lets create table t1 in both databases.

mysql> use db1;
Database changed
mysql> create table t1 (id int(10), name varchar(25));
Query OK, 0 rows affected (0.00 sec)

mysql> use db2;
Database changed
mysql> create table t1 (id int(10), name varchar(25));
Query OK, 0 rows affected (0.00 sec)

Now, here goes my trigger. The following trigger inserts a new record into t1 table in database db2 right after a record is inserted into table t1 in database db1.

mysql> DELIMITER &&

mysql> create trigger after_insert_db1_t1 after insert on `db1`.t1 for each row begin insert into `db2`.t1(id, name) values (NEW.id, NEW.name); END&&
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

If you have problem understanding the above trigger, then you should check our basic mysql trigger example. Its simple and straight.

Now, its time to insert a record in t1 table in db1 database.

mysql> use db1;
Database changed
mysql> insert into t1 (id, name) values (‘1’, ‘YOUR-NAME-HERE’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+——+—————-+
| id | name |
+——+—————-+
| 1 | YOUR-NAME-HERE |
+——+—————-+
1 row in set (0.00 sec)

Alright, the record is now inserted in t1 table in db1 database. Time to check if the trigger worked. Just go to db2 database and check t1 table to find out the answer. If everything goes fine, you should see the record there too.

mysql> use db2;
mysql> select * from t1;
+——+—————-+
| id | name |
+——+—————-+
| 1 | YOUR-NAME-HERE |
+——+—————-+

So, that ends things. Hope you found this example useful.

Share our stuff and help us grow. Need help to create something new or fix something old? Contact us to know if we can help 🙂

Related Posts:

3 Responses to Create Mysql Trigger across Different Databases with Examples

  1. kev says:

    do I need to have the same user for my databases when I trigger between tables in different databases?

  2. ipank says:

    how about update statement ? please share…

  3. Mujuzi Vincent says:

    Thank you for this wonderful posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Why ask?