July 7th, 2012, 12:53 PM
Join Date: Jun 2012
Time spent in forums: 31 m 14 sec
Reputation Power: 0
CONCAT not working
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...
CREATE TRIGGER bi_t1 BEFORE INSERT ON t1 FOR EACH ROW
SET NEW.rcpt_no= CONCAT('MIS',new.id);
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