mysql triggering is not working

Question

I have a table called login having 3 fields 'id','token' and 'status' . id is auto generating . I want to store the auto generated 'id' in the field 'status' whenever an insert into that table happens using trigger.Can i do this using insert after trigger?This is my first go at triggers, so any help would be greatly appreciated. My code is given bellow..

CREATE TRIGGER ins_masterid AFTER INSERT ON `login`
FOR EACH ROW BEGIN
SET NEW.status = NEW.id;

Answer 1

try this

CREATE TRIGGER ins_masterid
BEFORE INSERT ON login 
FOR EACH ROW SET new.status = (select max(id)+1 from login);

But you should update the status of first row manually.

Answer 2

Whenever you want to use "SET NEW.column" in your trigger, please note that you CANNOT use this with the AFTER the action, and must use it BEFORE the action.

delimiter |

CREATE TRIGGER ins_masterid BEFORE INSERT ON `login`
FOR EACH ROW BEGIN
SET NEW.status = NEW.id;
  END;
|

delimiter ;