want to write trigger for two different databases in Mysql

Question

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

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 ;

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

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?

UPDATE:

With delimeter without delimeter

Can anybody help me to get rid of it.

Thanks

No one there who can solve my problem?

pkachhia
– pkachhia

2012-09-24 08:40:46 +00:00
Commented Sep 24, 2012 at 8:40 — pkachhia
– pkachhia, Commented Sep 24, 2012 at 8:40

Germann Arlington · Accepted Answer · 2012-09-25 15:10:13Z

1

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

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

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

Edited code:

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;

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

edited Sep 25, 2012 at 15:10

answered Sep 21, 2012 at 8:26

Germann Arlington

3,3532 gold badges19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

pkachhia Over a year ago

Thanks for your reply, my trigger is working correctly when I have applied only on one database, but when I have write same kind of trigger on both the databases, It gives error.

Germann Arlington Over a year ago

You should add exists type check around your insert statement

pkachhia Over a year ago

I know that I have to write something like this but I am very new to trigger, so can you explain it briefly, so I can use exists type check?

pkachhia Over a year ago

Thanks for your code, I have tried your code. I have write trigger into both databases, and tried to insert into one database table but It was not inserted into other table of other database.

Germann Arlington Over a year ago

Did you check if record already exists for the NEW.employeeNumber in otherDB.otherTable?

|

Collectives™ on Stack Overflow

want to write trigger for two different databases in Mysql

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related