Trigger to update table based on another table data in different databases

Overview

Triggers are a specialized type of stored procedure that can be written to act on a table action such as an INSERT, UPDATE, or DELETE. If overused, they can potentially lead to performance issues such as blocking and, if they're not written correctly, you could be losing data.

Explanation

Triggers are commonly used to perform auditing actions, to maintain table integrity in place of native constraints such as foreign keys and check constraints, and to perform other post DML processing. Triggers operate under the scope of a transaction so if a table were updated, for instance, the update would occur and the trigger would fire. While the trigger is at work, the transaction would not be committed until the trigger completed (or rolled back in the case of failure). If a lot of processing is being done in the trigger, locks will be held until the trigger completes. This is an important point to note: Triggers extend the life of a transaction. Also, due to their stealthiness, they can make troubleshooting data issues difficult and tedious.

The use of triggers to accomplish common integrity checking is probably not a good idea since they do extend transaction life. Moreover, if there is an integrity violation, a ROLLBACK on any modified data will have to occur which can potentially cause a performance bottleneck as the application waits for the rollback to complete. In contrast, native constraints do their checks prior to any modification and, as a result, do not cause a ROLLBACK to occur if a violation occurs.

When triggers fire, there are virtual tables that hold the values of the data before and after the modification. These tables are called inserted and deleted. When accessing these virtual tables within trigger code, you should work on their data as a set. One common mistake I see over and over and over in trigger code: a trigger is written with the assumption it will always work on a single row at a time. 

CREATE TRIGGER `db1`.trig3 AFTER UPDATE ON `db1`.t1 FOR EACH ROW UPDATE `db2`.t1 SET NAME=new.name,age=new.age WHERE id=new.id;

See Below example:

CREATE DATABASE db1;
CREATE DATABASE db2;
USE db1;
CREATE TABLE t1(id INT(10), NAME VARCHAR(25));
USE db2;
CREATE TABLE t1(id INT(10), NAME VARCHAR(25));
DROP TRIGGER trig3;

CREATE TRIGGER `db1`.trig3 AFTER UPDATE ON `db1`.t1 FOR EACH ROW UPDATE `db2`.t1 SET NAME=new.name,age=new.age WHERE id=new.id;