0

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;
DjangoDev
  • 889
  • 6
  • 16
  • 25

2 Answers2

1

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 ;
medina
  • 8,051
  • 4
  • 25
  • 24
  • i tried this.but the status field is allways getting '0'.what to do? – DjangoDev Apr 17 '13 at 03:58
  • yes, you right mangus. I've done a few searches here and the only solutions I've found so far is this related issue: http://stackoverflow.com/questions/1211792/mysql-trigger-to-update-a-field-to-the-value-of-id . Good luck! – medina Apr 17 '13 at 04:16
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.

laksys
  • 3,228
  • 4
  • 27
  • 38