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..

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.

Answers


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:

  1. Change your default schema to the CollectionDB database before attempting to create the trigger:

    USE CollectionDB;
    CREATE TRIGGER UpdateSynch ...
    
  2. Explicitly specify in the CREATE TRIGGER statement the database in which you want it to be created:

    CREATE TRIGGER CollectionDB.UpdateSynch ...
    

Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.