1

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?

0

My initial guess would be based on the trigger's flow. Why ?

According to the Book

MySQL Stored Procedure Programming

Chapter 11 Page 251 Paragraph 3 says the following:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.

From the this statement, this is what I understand: Since the DML is executed and the DML is still spoken of in the future imperfect tense (I am not an English teacher), the new values are really not available to be read in a SELECT since the trigger is not done.

In your case, you are doing an AFTER UPDATE on phpbb_rathermenacing.phpbb_bbdkp_memberlist. With the trigger being 99.99999999% done, you are attempting to do the following in the third clause of the IF statement:

        ((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)

You are asking for the count and state of the table phpbb_bbdkp_memberlist that has not yet been changed. Regardless of the Storage Engine, I am very sure you will never get a response because the trigger has to finish in order for the Storage Engine to even think about posting changes to disk.

I have two old posts where I discuss why a trigger is no place for business intelligence:

SUGGESTIONS

  • Make that trigger a stored procedure
  • Remove the AFTER UPDATE trigger
  • Run the stored procedure after the UPDATE in your application.
  • While good to know, unfortunately, this particular case doesn't give me the option of calling a stored procedure after the update. I'm injecting functionality in to one systems functionality (which I can't alter) to impact another system in the way I need. As it turns out, this was actually a simple missing logic clause prior to the Insert statement that resulted in it never firing coupled with the debugger apparently not working properly. – AJ Henderson Aug 14 '14 at 21:12
0

In this case, it turns out that the problem was a misdiagnosis of symptoms. The debugger was simply failing to function for some as-yet unknown reason and the trigger was failing to do anything because it was properly executing its code. The conditional before the INSERT statement was missing a check to ensure it was only looking at the current user's records, so it always thought there was someone in the group and never inserted despite the user not being in the group yet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.