MySQL - AFTER UPDATE Trigger between Two Databases
database, mysql, schema, triggers
Solution
You have to create the trigger in the same database as the target table, i.e. `CollectionDB`; however, you must currently have another database selected as your default schema.
Therefore, either:
Change your default schema to the `CollectionDB` database before attempting to create the trigger:
USE CollectionDB;
CREATE TRIGGER UpdateSynch ...
Explicitly specify in the `CREATE TRIGGER` statement the database in which you want it to be created:
CREATE TRIGGER CollectionDB.UpdateSynch ...
Problem
Been having some issues with this for a few hours. I'm by no means a MySQL guy and have been looking for a solution but no luck. Here's essentially what I have.. T1: `CollectionDB`.source ``` indexName | status_id Test | 11 ``` T2: `ManagementConsole`.MC_SCHEDULE ``` NAME | ACTIVE Test | 0 ``` Basically, the trigger I need should run after T1.status_id has changed from any number to 8. When it runs, it should find T2.NAME that matches the corresponding T1.indexName and change ACTIVE from 0 to 1. Here's what I have thus far. ``` DELIMITER && CREATE TRIGGER `UpdateSynch` AFTER UPDATE ON `CollectionDB`.source FOR EACH ROW BEGIN UPDATE `ManagementConsole`.MC_SCHEDULE AS T1 SET T1.ACTIVE = 1 WHERE (NOW.indexName = T1.NAME) AND ((OLD.status_id != 8) AND (NOW.status_id = 8)); END&& ``` I'm getting a 'Trigger in wrong schema' error, which makes sense since I'm trying to make an update in a different schema. Is there any way around this? Thanks in advance.