BEGIN

DECLARE id_i int(11);
DECLARE email_i varchar(100);
DECLARE role_i varchar(100);

SELECT NEW.ID, NEW.user_email, wpDusermeta.meta_value
INTO id_i, email_i, role_i
FROM wpDusers

INNER JOIN wpDusermeta ON (wpDusers/NEW).ID = wpDusermeta.user_id
WHERE wpDusermeta.meta_key = 'wpDcapabilities'
order by wpDusers.ID desc limit 1;

IF (role_i like '%administrator%') THEN
INSERT INTO ST_users (wpid, email, wp_role, role, role_id)
VALUES (id_i, email_i, role_i, 'administrator', 1);

ELSEIF (role_i like '%parent%') THEN
INSERT INTO ST_users (wpid, email, wp_role, role, role_id)
VALUES (id_i, email_i, role_i, 'parent', 2);

ELSEIF (role_i like '%student%') THEN
INSERT INTO ST_users (wpid, email, wp_role, role, role_id)
VALUES (id_i, email_i, role_i, 'student', 3);

ELSEIF (role_i like '%pupil%') THEN
INSERT INTO ST_users (wpid, email, wp_role, role, role_id)
VALUES (id_i, email_i, role_i, 'pupil', 4);

ELSEIF (role_i like '%tutor%') THEN
INSERT INTO ST_users (wpid, email, wp_role, role, role_id)
VALUES (id_i, email_i, role_i, 'tutor', 5);

ELSE
INSERT INTO ST_users (wpid, email, wp_role, role, role_id)
VALUES (id_i, email_i, role_i, 'user', 0);

END IF;
END