3

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

pkachhia
  • 1,876
  • 1
  • 19
  • 30

1 Answers1

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

Germann Arlington
  • 3,315
  • 2
  • 17
  • 19
  • 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. – pkachhia Sep 21 '12 at 08:28
  • You should add `exists` type check around your `insert` statement – Germann Arlington Sep 21 '12 at 08:33
  • 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 Sep 21 '12 at 08:35
  • 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. – pkachhia Sep 21 '12 at 08:51
  • Did you check if record already exists for the NEW.employeeNumber in otherDB.otherTable? – Germann Arlington Sep 21 '12 at 09:12
  • Yes, I have written the same trigger you have suggested. – pkachhia Sep 21 '12 at 09:15
  • No, I meant did you run `SELECT COUNT(*) FROM otherDB.otherTable WHERE employeeNumber = NEW.employeeNumber` yourself? It is possible that the record already exist in the `otherDB.otherTable` because your earlier inserts did not replicate data correctly. – Germann Arlington Sep 21 '12 at 09:19
  • How can I run this query with NEW keyword? I have checked it manually and the employeeNumber does not exist into otherDB.otherTable. – pkachhia Sep 21 '12 at 09:24
  • You will need to get the value of employeeNumber that does not get inserted. – Germann Arlington Sep 21 '12 at 09:41
  • but it is achieved by using NEW keyword into trigger, As per your suggested trigger, it will check that existence of employeeNumber into otherDb.othertable.I have checked into both tables both have same empplyeeID (ie. 5) before fire the insert query, but after it trigger not inserted into second database. – pkachhia Sep 21 '12 at 09:52
  • To test the triggers you need to insert a record into **ANY** of the two DB tables with the `employeeNumber` that does **NOT** exist in either of them. I.e. **completely unique** new `employeeNumber`. The actual insert will succeed on the table and this in turn will fire the trigger. The trigger will check if the newly inserted `employeeNumber` exists in the `otherDB.otherTable` and if it does **NOT** exist it will insert a copy there. – Germann Arlington Sep 21 '12 at 10:10
  • Yes I have done the same thing and replied you. But the trigger does not insert copy to other table. – pkachhia Sep 21 '12 at 10:16
  • It should have been `IF NOT EXISTS(SELECT employeeNumber FROM otherDB.otherTable WHERE employeeNumber = NEW.employeeNumber)` in your triggers. My mistake but you should check such things yourself too – Germann Arlington Sep 21 '12 at 10:27
  • Ok Thanks +1 for your answer I have updated the trigger as per your suggestion and it works correctly. Now can you please tell me why this trigger not created on my testing server with mysql 5.0.45? – pkachhia Sep 21 '12 at 10:56
  • Post http://stackoverflow.com/questions/1102109/mysql-delimiter-syntax-errors seems to imply that `DELIMITER;` should be changed to `DELIMITER ;` (extra space) to work on MySQL 5.0.45 – Germann Arlington Sep 21 '12 at 11:03
  • Yes I have already given extra space ( I have edited my code into question) but it is not working on MYSQL 5.0.45 – pkachhia Sep 21 '12 at 11:08
  • Please check my updates, I have uploaded both the screen shots of mysql errors,trigger with delimeter and without delimeter. Can you tell me what is the problem? – pkachhia Sep 21 '12 at 11:25