mysql - ReSet all password status to zero using trigger -

i optimizing login system in maintain previous passwords of user, schema of pwd table maintaining passwords follows:

here userid refers primary key of user table. if status attribute 1 means row has marking current password if 0 previous password.

how can write trigger if new entry user made in pwd table, previous status of user gets set 0 , new value remain 1 default value of status attribute. doing @ application level.

it won't possible because require have mutating (changing same table it's being fired upon) trigger prohibited in mysql.

you can create stored procedure though, still need call client code explicitly.

now better schema design keep current passwords in 1 table (presumably in users table) , previous in other (i.e. pwd_history). not makes queries current passwords faster allows use trigger if choose to.

delimiter // create trigger tg_pwd_history  after update on users each row begin   if not new.pwd <=> old.pwd     insert pwd_history (`userid`, `pwd`, `ts`)     values (new.id, old.pwd, now());   end if; end// delimiter ; 

here sqlfiddle demo

now hope you're not storing them in plain text.