To check values in other databases using trigger

Question

Is it possible to check whether a particular value in a column exists in other databases using trigger? These two databases are located inside the same MYSQL instance. Specifically, what I want to do is this:

1. Before a row is added to a table ( Document_Index_table) inside Database A ( Document_DB).
2. A trigger is fired. This trigger carries the one of the column value (usr_id) inside the row and pass it to Database B ( User_Control_DB).
3. Based on the values, User_Control_DB will check whether the usr_id exists in column usr_id of the table (Usr_Information).
4. If exists, then return a true to Document_DB and the row in 1. is allowed to add to the Document_DB.
5. If not, then an error is issued. No row is added to Document_DB.

How can this be done, if it can be done at all?

Edit: Both databases are MySQL databases

Answer 1

So, I'm a complete novice at database development, but you could do something like this:

Create a 'Before' insert trigger on your document_index_table. The trigger does something like this:

declare numRows integer;
select count(*) from user_control_db.usr_information where usr_id = NEW.usr_id into num_rows;
if (numRows > 0) then
   call NonExistentProc();
end if;

I believe that this would accomplish what you wanted. It'll produce an error like "PROCEDURE documentdb.NonExistenProc does not exist" and skip the insert if there isn't at least one row that has the matching usr id in the user control db.

Again, I'm a novice at this DB stuff so there might be a more elegant way, but this worked for my single test case.

Hope that helps.