想在MySQL中为两个不同的数据库编写触发器 [英] want to write trigger for two different databases in Mysql

问题描述

有什么方法可以在Mysql中的两个不同数据库上创建触发器吗?我的要求是:-

Is there any way to create triggers on two different databases in Mysql? my requirement is like:-

database: test1 -> table: tmp1
database: test2 -> table: tmp2

现在我必须在test1上使用触发器，插入操作发生在tmp1上，必须将一个值插入到test2数据库的tmp2中.并反之亦然. 即在test2数据库的tmp2表上再触发一个触发器，如果​​将其插入到tmp2中，然后插入到test1数据库的tmp1表中.

now I have to use trigger on test1 insert operation happens on tmp1 a value has to be inserted into tmp2 of test2 database. And also vice a verse. i.e. one more trigger on tmp2 table of test2 database, if insert into tmp2 then inserted into tmp1 table of test1 database.

我试图在两者上都编写触发器，但我认为它将陷入循环以插入其他表.

I have tried to write the trigger on both but I think it will goes into loop to insert each other tables.

DELIMITER $$
CREATE TRIGGER trigger_ad_t1 AFTER insert ON `test1`.tmp1 
FOR EACH ROW
Begin
  INSERT INTO `test2`.tmp2 VALUES (NEW.employeeNumber,New.fname,New.lname)
END$$
DELIMITER ;

在插入到tmp2表之后，为插入到tmp1而编写的触发器类型相同.

same type of trigger written for insert into tmp1 after insert into tmp2 table.

还有另一件事，我已经在具有mysql 5.1.63的本地计算机上测试了该触发器，但是当我在具有mysql 5.0.45的测试服务器上尝试此触发器时，它给了我语法错误(1064).不知道是什么问题?

One more thing I have tested this trigger on my local pc which has mysql 5.1.63 but when I am trying this trigger on my testing server which has mysql 5.0.45 then it gives me syntax error(1064). Don't know what is the problem?

更新:

有人可以帮我摆脱它吗?

Can anybody help me to get rid of it.

谢谢

推荐答案

在触发器中使用标准表名.
即
db1.test1.*和d2.test2.*

Use fully qualified table names in your trigger.
I.e.
db1.test1.* and d2.test2.*

P.S.再看完您的SQL之后，我意识到您已经 ARE 进行了上述操作.

P.S. After looking at your SQL one more time I realised that you ARE doing the above already.

注释字段仅限于邮政编码，因此，这是防止无限插入循环的方法(假设employeeNumber是唯一键):

Comment field is to restrictive to post code, so here is how you prevent the endless insert loop (assuming employeeNumber is unique key):

修改后的代码:

IF NOT EXISTS(SELECT employeeNumber FROM otherDB.otherTable WHERE employeeNumber = NEW.employeeNumber) THEN
INSERT INTO otherDB.otherTable VALUES (NEW.employeeNumber,New.fname,New.lname)
END IF;

在原始提供的代码中需要更正: ... EXISTS(SELECT * FROM otherDB.otherTable ...)被替换为
... EXISTS(SELECT employeeNumber FROM otherDB.otherTable ...)
原因是第一个查询将始终返回true，因为内部查询SELECT * FROM ...始终返回一个包含结果数的记录=>
EXISTS(SELECT * FROM ...)始终为true

Correction was needed in the code provided originally: ... EXISTS(SELECT * FROM otherDB.otherTable ...) is replaced with
... EXISTS(SELECT employeeNumber FROM otherDB.otherTable ...)
The reason being that the first query will always return true because the inner query SELECT * FROM ... always returns one record containing the number of results =>
EXISTS(SELECT * FROM ...) is always true

这篇关于想在MySQL中为两个不同的数据库编写触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！