SQL Trigger – Having one is messing up new SQL queries

I am able to manually execute the following code:

UPDATE pfields_cont p, members m
   SET m.member_group_id = 6
 WHERE p.member_id = m.member_id
   AND m.member_group_id != 6
   AND p.field_2 = 'Teacher';

However, if I try creating a Trigger with the same code as above, I get this error when creating a new user in my database:

Can’t update table ‘members’ in stored function/trigger because it is
already used by statement which invoked this stored function/trigger
(1442)

Can anybody help fix the trigger?

Thanks!

EDIT 1:

Full trigger definition:

CREATE TRIGGER `newTeacher` AFTER INSERT ON `members` FOR EACH ROW UPDATE
    pfields_cont p,
    members m
SET
    m.member_group_id = 6
WHERE
    p.member_id = m.member_id
    AND m.member_group_id != 6
    AND p.field_2 = 'Teacher'

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Since you cannot refer to the same table in a trigger’s definition (for reading or writing), the trigger handling can be simplified based on setting the member_group_id to 6 if field_2 is Teacher:

CREATE TRIGGER `newTeacher` BEFORE INSERT ON `members`
FOR EACH ROW UPDATE
BEGIN
    Declare fieldInfo varchar(30);

    SELECT field_2 INTO fieldInfo 
      FROM pfields_cont 
     WHERE pfields_cont.member_id = NEW.member_ID;

    IF fieldInfo = 'Teacher' THEN
        SET NEW.member_group_id = 6;
    END IF;
END;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0