CODEWITHSUNDEEP
mysqltriggersdatabase-trigger
KenobiBastila
KenobiBastila

Reputation: 721

How to set a trigger to insert in another table from another schema?

How to set a trigger to insert in another table from another schema ?

Brief explanation

Considering schema "S" and schemas "1" to "n". Consider that Schema "S" has table Users and table Company. The table users has a relationship 1:1 to table Company. Every User is in a company. The table Company has the column "schema" (varchar(255))

Schema "S" has the table Users and table Company defined. Every other schema should have acopy of table Users.

However the table Users is only modified on schema S.

Everytime table Users in Schema "S" is modified, I want to replicate the changes to the schema 'n' IF and only IF the column "schema" in table "Company" == 'n'.

This restriction makes this question different from the other questions!

Ah yes:

Illustration of the issue

Example

Consider the Table Users has the following columns:

Consider the table Company has the following columns:

When I make an insert on the Users table on schema "S", I want the following to happen:

    if User.Company.schema == "schema A"{

        replicate to schema "A";

    } else if User.Company.schema == "schema N"{
        replicate to schema "N";
    }
....

Im confused on how to do that logic in the code below:

CREATE DEFINER=`root`@`localhost` TRIGGER `user_AFTER_INSERT` AFTER INSERT ON `user` FOR EACH ROW BEGIN

END

Upvotes: 1

Views: 1856

Answers (2)

Nick
Nick

Reputation: 147146

You will need to use dynamic SQL to build an INSERT query to the other database:

CREATE DEFINER=`root`@`localhost` TRIGGER `user_AFTER_INSERT` AFTER INSERT ON `user` 
FOR EACH ROW 
BEGIN
  SELECT schema INTO @schema FROM Company WHERE Id = NEW.idCompany;
  SET @sql = CONCAT('INSERT INTO `', @schema, '`.`Users` (id, username, dateBirth, idCompany) VALUES(?, ?, ?, ?)';
  PREPARE stmt FROM @sql;
  SET @id = NEW.id;
  SET @username = NEW.username;
  SET @dateBirth = NEW.dateBirth;
  SET @idCompany = NEW.idCompany;
  EXECUTE stmt USING @id, @username, @dateBirth, @idCompany;
  DEALLOCATE PREPARE stmt;
END

Upvotes: 1

TFBW
TFBW

Reputation: 1019

I know nothing of Hybernate, but given that you only want these tables to be read-only, views may work. Try something like the following (some names have been fudged due to lack of detail in the question) in lieu of your CREATE TABLE statements for the Company tables.

CREATE VIEW users AS
SELECT `id`, `username`, `dateBirth`, `schema`
FROM S.users
WHERE `schema` = 'schema X';

If this works, it saves all the overhead of replication.

Upvotes: 1

Related Questions