MySQL trigger loop

Question

Hey all

I am going through the pain stacking process of sorting out someone else code.

So I am decided to recreate a new database to sit alongside the old one then to use triggers to transfer data between both tables.

Now I have an issue with a it looping IE

A trigger on each table to update the other. Once one updates it should update the other but as both tables have triggers it just will loop which will cause an issue.

Is their a way to stop this from happening ?

Hope this makes sense and hope you can advise.

Answer 1

You should be making entries in one db and using the trigger to copy that data to the second db. Having said that you use a check for the existence of the data and exit the trigger. Basically an if record exist do nothing. This site has a good tutorial:

http://www.databasedesign-resource.com/mysql-triggers.html

You may aloso want to read up on triggers in the mySQL manual:

http://dev.mysql.com/doc/refman/5.0/en/triggers.hthl