MySQL - AFTER UPDATE Trigger between Two Databases
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..
indexName | status_id Test | 11
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.
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.
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 ...