I need a trigger to create id's in my sql database with a string and some zeros

I'm currently using this trigger which adds id's with 3 zeros and two zeros and then the id from the sequences table.

BEGIN
  INSERT INTO sequences VALUES (NULL);
  SET NEW.deelnemernr = CONCAT('ztmr16', LPAD(LAST_INSERT_ID(), 3, '0'));
END

I changed the 3 to 4 but then it didn't increment the id anymore, resulting in and multiple id error. It stayed at ztmr16000. So what can I do to add more zeros and still get the id from the sequencestable?

Answers


The MySQL LPAD function limits the number of characters returned to the specified length.

The specification is a bit unclear, what you are trying to achieve.

If I need a fixed length string with leading zeros, my approach would be to prepend a boatload of zeros to my value, and then take the rightmost string, effectively lopping off extra zeros from the front.

To format a non-negative integer value val into a string that is ten characters in length, with the leading characters as zeros, I'd do something like this:

   RIGHT(CONCAT('000000000',val),10)

As a demonstration:

SELECT RIGHT(CONCAT('000000000','123456789'),10)  --> 0123456789
SELECT RIGHT(CONCAT('000000000','12345'),10)      --> 0000012345

Also, I'd be cognizant of the maximum length allowed in the column I was populating, and be sure that the length of the value I was generating didn't exceed that, to avoid data truncation.


If the value being returned isn't be truncated when it's inserted into the column, then what I think the behavior you observe is due to the value returned from LAST_INSERT_ID() exceeding 1000.

Note that for a non-negative integer value val, the expression

 LPAD(val,3,'0') 

will allow at most 1000 distinct values. LPAD (as I noted earlier) restricts the length of the returned string. In this example, to three characters. As a demonstration of the behavior:

   SELECT LPAD(    21,3,'0')    --> 021
   SELECT LPAD(   321,3,'0')    --> 321
   SELECT LPAD( 54321,3,'0')    --> 543
   SELECT LPAD( 54387,3,'0')    --> 543

There's nothing illegal with doing that. But you're going to be in trouble if you depend on that to generate "unique" values.


FOLLOWUP

As stated, the specification ...

"adds id's with 3 zeros and two zeros and then the id from the sequences table."

is very unclear. What is it exactly that you want to achieve? Consider providing some examples. It doesn't seem like there's an issue concatenating something to those first five fixed characters. The issue seems to be with getting the id value "formatted" to your specification

This is just a guess of what you are trying to achieve:

id value  formatted return
--------  ----------------
       1  0001
       9  0009
      22  0022
      99  0099 
     333  0333
    4444  4444
   55555  55555
  666666  666666

You could achieve that with something like this:

BEGIN
  DECLARE v_id BIGINT;
  INSERT INTO sequences VALUES (NULL);
  SELECT LAST_INSERT_ID() INTO v_id;
  IF ( v_id <= 9999 ) THEN
    SET NEW.deelnemernr = CONCAT('ztmr16',LPAD(v_id,4,'0'));
  ELSE
    SET NEW.deelnemernr = CONCAT('ztmr16',v_id);
  END IF;
END

Need Your Help

new email locks outlook, forces email window as topMost

c# outlook topmost

I am using the following code to read an email address from a DataGridView then create an Outlook email. This works perfectly, except that the new email is set as topMost and/or opened as a dialog ...