I have the following After Update trigger on a MySQL table:
CREATE
DEFINER = 'root'@'localhost'
TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD
AFTER UPDATE
ON phpbb_rathermenacing.phpbb_bbdkp_memberlist
FOR EACH ROW
BEGIN
IF NEW.member_confirmed = 1 AND
NEW.member_rank_id <> OLD.member_rank_id AND
((select min(m.member_rank_id)
from phpbb_bbdkp_memberlist m
WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
m.member_guild_id = NEW.member_guild_id AND
m.member_id != NEW.member_id) >= new.member_rank_id OR
(select count(m.member_rank_id)
from phpbb_bbdkp_memberlist m
WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
m.member_guild_id = NEW.member_guild_id AND
m.member_id != NEW.member_id) = 0)
THEN
BEGIN
IF (SELECT count(*)
FROM phpbb_user_group ug
WHERE ug.group_id =
(SELECT mr.phpbb_group_id
FROM phpbb_bbdkp_member_ranks mr
WHERE mr.guild_id = OLD.member_guild_id AND
mr.rank_id = OLD.member_rank_id) AND
ug.group_leader = 0 ) > 0
THEN
UPDATE phpbb_user_group ug
SET ug.group_id = (SELECT mr.phpbb_group_id
FROM phpbb_bbdkp_member_ranks mr
WHERE mr.guild_id = NEW.member_guild_id AND
mr.rank_id = NEW.member_rank_id),
ug.user_pending = 0
WHERE ug.user_id = NEW.phpbb_user_id AND
ug.group_id = (SELECT mr.phpbb_group_id
FROM phpbb_bbdkp_member_ranks mr
WHERE mr.guild_id = OLD.member_guild_id AND
mr.rank_id = OLD.member_rank_id);
ELSE
IF(SELECT count(*)
FROM phpbb_user_group ug
WHERE ug.group_id = (SELECT mr.phpbb_group_id
FROM phpbb_bbdkp_member_ranks mr
WHERE mr.guild_id = NEW.member_guild_id AND
mr.rank_id = NEW.member_rank_id)) = 0
THEN
INSERT INTO phpbb_user_group (group_id, user_id, group_leader, user_pending)
VALUES ((SELECT mr.phpbb_group_id
FROM phpbb_bbdkp_member_ranks mr
WHERE mr.guild_id = NEW.member_guild_id AND
mr.rank_id = NEW.member_rank_id),
m.phpbb_user_id, 0, 0);
END IF;
END IF;
END;
END IF;
END
Basically, the trigger is checking to see if one type of group that the user is associated with has been changed, and in the event that it has been changed, it makes an alteration to set a reference for another type of group.
The problem I'm having is that while the trigger saves perfectly fine, nothing seems to happen when I try doing updates. I can change the rank_id for a memberlist row, but it just updates the row. I've even tried debugging with dbForge Studio and while it will stop on the UPDATE query I use to actually make the update, stepping in to the UPDATE method does not appear to hit the trigger.
What possibilities could be causing the debugging break points to never be hit and no apparent behavior from the trigger to be seen?
