codice:
DROP PROCEDURE IF EXISTS Changerole; delimiter $ CREATE PROCEDURE Changerole (OldRole CHAR(8), Newrole CHAR(8), IdUser INT) BEGIN IF (OldRole="admin" AND NewRole="mod") THEN DELETE FROM administrators WHERE iduser=IdUser; INSERT INTO moderators (iduser) VALUES (IdUser); END IF; IF (OldRole="admin" AND NewRole="user") THEN DELETE FROM administrators WHERE iduser=IdUser; INSERT INTO normalusers (iduser) VALUES (IdUser); END IF; IF (OldRole="mod" AND NewRole="admin") THEN DELETE FROM moderators WHERE iduser=IdUser; INSERT INTO administrators (iduser) VALUES (IdUser); END IF; IF (OldRole="mod" AND NewRole="user") THEN DELETE FROM moderators WHERE iduser=IdUser; INSERT INTO normalusers (iduser) VALUES (IdUser); END IF; IF (OldRole="user" AND NewRole="admin") THEN DELETE FROM normalusers WHERE iduser=IdUser; INSERT INTO administrators (iduser) VALUES (IdUser); END IF; IF (OldRole="user" AND NewRole="mod") THEN DELETE FROM normalusers WHERE iduser=IdUser; INSERT INTO moderators (iduser) VALUES (IdUser); END IF; END; $ delimiter ;
Praticamente questa procedura mi cancella da una certa tabella e mette la stessa riga in un altra. Il problema è che l'insert funziona correttamente, ma quando fa il delete cancella tutta la tabella invece che solo quella riga. Perché?