I have one table t1. Structure is like this...


CREATE TABLE IF NOT EXISTS `t1` (
`ID` int(3) NOT NULL AUTO_INCREMENT,
`RCPT_DATE` date NOT NULL,
`AMOUNT` int(5) NOT NULL DEFAULT '0',
`RCPT_NO` varchar(6) DEFAULT '',
PRIMARY KEY (`MISC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Now I have one trigger which will set rcpt_no as concat of id and one string as follows...

DELIMITER $$
CREATE TRIGGER bi_t1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN

SET NEW.rcpt_no= CONCAT('MIS',new.id);
end $$

DELIMITER ;

Trigger is working but not like as I want.

INSERT INTO `t1` (`RCPT_DATE`, `AMOUNT`) VALUES ('2012-07-07', 132);

But select * from t1 shows --

1 2012-07-07 132 MR0
As id is 1 then RCPT_NO should be MIS001 and not MIS0.

please help. Thanks in advance