[Catalyst] Some guidance needed please

I'm not sure I would structure the code exactly like you did. I think I
would add a method to the User class like:

package My::Schema::User;
...
sub set_password { txn_do(update current password, update used
passwords) }

Then you can say something like:

$c->model('DBIC::Users')->find(42)->set_password('foobarbaz');

and have everything work.

Finally, if you want your create_limited to work, try:

package My::ResultSet::UsedPassword;
use base 'DBIx::Class::ResultSet';
sub create_limited { ... };

package My::Schema::UsedPassword;
__PACKAGE__->resultset_class('My::ResultSet::UsedPassword');

Regards,
Jonathan Rockway