Progress - More Info - More Questions

[John Hundley]

Ok so I have been playing around with this and I have some more questions, first a simple example of the triggers:

USE db1;

DROP TRIGGER IF EXISTS test_trigger;

CREATE TRIGGER test_trigger AFTER UPDATE ON db1.packages
FOR EACH ROW
UPDATE db2.packages p SET p.queue_order = NEW.repetition WHERE p.id = NEW.id;

USE db2;

DROP TRIGGER IF EXISTS test_trigger_2;

CREATE TRIGGER test_trigger_2 AFTER UPDATE ON db2.packages
FOR EACH ROW
UPDATE db1.packages p SET p.repetition = NEW.queue_order WHERE p.id = NEW.id;


Now when I update the field in db1 I am trying to synchronize everything works but mysql throws the following error on db1:

Can't update table 'packages' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

This is because:

update db1 -> test_trigger fires, updates db2 -> test_trigger_2 fires, attempts to update db1 -> exception

Here are my questions:

Is there a way to trap the error and ignore it? (I have considered other options like dropping and recreating triggers but trapping seems to be the best solution).

Obviously I am going to need to put more logic into the triggers so I want to add a begin end block. When I attempt to put stuff in a begin end block I get a mysql syntax error so something like this:

CREATE TRIGGER test_trigger AFTER UPDATE ON epd.packages
FOR EACH ROW
BEGIN
UPDATE helius.packages p SET p.queue_order = NEW.repetition WHERE p.id = NEW.id;
END;

throws this error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

I don't understand what is wrong with this syntax any ideas? BTW I am on version 5.0.51