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
Can anybody help fix the trigger?
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'
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.
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
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;