Jun 18

mysql – how to stop infinite loop in mysql trigger

i have met such situation:two triggers on two talbes,which operate on each other,lead to circular dependencies.sometimes it may get mysql query failed:

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

Source code    
delimiter //
DROP TRIGGER IF EXISTS in_bbs_cms //
CREATE TRIGGER `in_bbs_cms` AFTER INSERT ON `ucenter_members`
FOR EACH ROW BEGIN
	INSERT INTO `ucenter_members` (username, password, email, regip, regdate, random, ucuserid) VALUES(NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.salt, NEW.uid);
END
//
 
-- FOR NEW RECORD
DROP TRIGGER IF EXISTS in_cms_bbs //
CREATE TRIGGER `in_cms_bbs` AFTER INSERT ON `v9_member`
FOR EACH ROW BEGIN
	INSERT INTO v9_member (username, password, email, regip, regdate, salt) VALUES (NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.encrypt);
END
//

 

to avoid this problem, we need to make use of mysql global variable as a flag to trigger.

Source code    
delimiter //
DROP TRIGGER IF EXISTS in_bbs_cms //
CREATE TRIGGER `in_bbs_cms` AFTER INSERT ON `ucenter_members`
FOR EACH ROW BEGIN
	IF @TRIGGERED = NULL THEN
		SET @TRIGGERED = TRUE;
		INSERT INTO `ucenter_members` (username, password, email, regip, regdate, random, ucuserid) VALUES(NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.salt, NEW.uid);
		SET @TRIGGERED = NULL;
	END IF;
END
//
 
-- FOR NEW RECORD
DROP TRIGGER IF EXISTS in_cms_bbs //
CREATE TRIGGER `in_cms_bbs` AFTER INSERT ON `v9_member`
FOR EACH ROW BEGIN
	IF @TRIGGERED = NULL THEN
		SET @TRIGGERED = TRUE;
		INSERT INTO v9_member (username, password, email, regip, regdate, salt) VALUES (NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.encrypt);
		SET @TRIGGERED = NULL;
	END IF;
END
//