Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: 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...

    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
    Last edited by shersaha; 07-07-12 at 11:27. Reason: correction

  2. #2
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    It looks like you can't access the auto increment value directly using the before insert trigger. As part of your trigger you could try to select the current auto increment for the table, and then add one as that should be the next value.
    ____________________
    Juacala - Web Application Developer, Eliacom, Inc.
    MySQL GUI Tools for Online Applications: Eliacom
    Last edited by juacala; 07-23-12 at 19:13.

  3. #3
    Join Date
    Sep 2007
    Posts
    18
    Probably you are right I am not getting auto increment value directly. As you suggested to get current auto increment for table using trigger , but not show how will I get it? But I have got it from information_schema of mysql table.

  4. #4
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    To get the information you can set a variable inside the trigger to the current auto increment value, and then add one to that, which should be the next value to get inserted:

    DECLARE x INT;
    SET x = (SELECT AUTO_INCREMENT FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA` = 'databasename' AND `TABLE_NAME`='tablename');
    SET NEW.rcpt_no= CONCAT('MIS',x+1);
    ____________________
    Juacala - Web Application Developer, Eliacom, Inc.
    MySQL GUI Tools for Online Applications: Eliacom

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    That would be open to race conditions though.

    What I want to know is WHY the rcpt_no column is needed at all?

    why don't you just use
    Code:
    SELECT
      CONCAT('MIS',id) as rcpt_no
    FROM
      yourtable
    then you aren't storing redundant data in the table.

  6. #6
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    guelphdad is right about the race condition problem. If you really want a concatenated column, the auto increment columns appear to be available if you do an AFTER INSERT trigger instead of a BEFORE INSERT. You could use that to update the row after it's been inserted. That should do the trick without the option of another row sneaking in while the trigger is pending.
    ____________________
    Juacala - Web Application Developer, Eliacom, Inc.
    MySQL GUI Tools for Online Applications: Eliacom

  7. #7
    Join Date
    Sep 2007
    Posts
    18
    I got auto_increment from information schema and doing well. But as suggested by guelphdad that there may be race condition occur then which will be better way to do it. BEFORE INSERT OR AFTER INSERT and why?

  8. #8
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    guelphdad is saying that he doesn't think you need to do this at all. I see his point.

    But, if you want to do it, use an AFTER INSERT trigger (which then gives you access to the auto_increment value), then you can then access the id using NEW.id, by doing something like

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

    I haven't tested this, but I think it should work.
    ____________________
    Juacala - Web Application Developer, Eliacom, Inc.
    MySQL GUI Tools for Online Applications: Eliacom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •