Trigger - updating column in same table before update

Hi. I have a table, simplified to three columns:
user_id, role_id, old_role

Superusers can change a users role_id, but I'd like to populate the old_role column with the previous role_id IF old_role is either 0 or the number is larger than the current role_id - hence if the content is as follows:
user_id: 11, role_id: 1, old_role: 0
and someone changes the role_id to 2, it will transfer the 1 to the old_role column before updating role_id, so the result becomes
user_id: 11, role_id: 2, old_role: 1

Then, if someone later decides to change the role_id again, say to 3 this time, it will NOT update the old_role column, since the number in old_role is less than the new role_id.

If the updating of the role_id is 1 again, I'd like to compare the two values, and reset old_role to 0 again, if possible.

This should all be done in a trigger, but I can't for the life of me get it right. I must admit I've mostly dabbled in simple triggers before, updating secondary tables and such, so I'm kind of a newbie on triggers.

I'm hoping someone can provide some example on how this can be done, or a working code, or point me to some tutorials which can teach me how to do this.

 

So, you want to allow the update of old role to 1 and no higher? Odd request, but I'm sure you have your reasons.
pseudo code that might work (the idea will work in MSSQL)
create trigger as t_oldRoleUpToOne on someTableName
instead of insert
create temp variables to hold values and
select into variables from inserted (@old_role = select old_role from inserted)
if @role_id (derived for inserted) = 0
begin
do stuff like update @old_role (@old_role = @old_role + 1)
end
else
begin
blah, blah, blah
insert (user_id,role_id,old_role) values (@user_id,@role_id,@old_role)
end
end

 

instead of insert is before insert and before update in MySQL, I think