SQL Trigger - Having one is messing up new SQL queries-CodePudding

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'

CodePudding user response：

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;