MySQL - Trigger before insert, only insert if col = another existing database name?

Question

I have a database (database1) and a table imei_db(IMEI VARCHAR(15), db VARCHAR(40)). I also have other databases (ex: database2) and I want a trigger that, whe inserting a row on database1.imei_db, it verifies before the actual insert if there is a database with a name equal to db field value.

For example:

Existing databases: database1, database2

Succeeds: INSERT INTO imei_db VALUES ("111222333444555","database2")

Fails: INSERT INTO imei_db VALUES ("111222333444555","database3")

A possible database that actually does not exist and was just used as an example of failure. On the example it would be a way to say that "for device `111222333444555`, I should use the `database3` to store its data". — MagisterMundus, May 27 '15 at 20:52
No, I mean like on the insert query. Only insert if there is a database with that name already. Actually, I've managed to do it; looks a bit low quality, though. I am posting it as an answer. — MagisterMundus, May 31 '15 at 01:14

score 0 · Accepted Answer · answered May 31 '15 at 01:17

This is how I managed to do it:

USE database1;
INSERT INTO `imei_db`(`IMEI`,`db`)
SELECT 111222333444555,"database2" FROM anytable
WHERE (SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'database2') AND (TABLE_NAME = 'table_expected_to_exist')) = 1
LIMIT 1;

MySQL - Trigger before insert, only insert if col = another existing database name?

1 Answers1