MySQL Update/Insert trigger auto-increment over group by

One option is not to have a distinct set of unique receipt numbers per category, per year. Instead, simply use the BuchungID as the receipt number (I'm assuming that value is unique in your table, and may very well be an actual autoincrement column in the actual database). This should guarantee a unique ID is already available, whether the user wants a receipt number or not.

However, I'll assume that you've got some business rule that prevents this simple solution from working, and you really need to generate distinct receipt numbers within each category/year pairing.

Simply looking at the maximum current receiptno and adding 1 doesn't guarantee uniqueness - it would be possible for two users to request a receipt number at the same time, both to check the current max for the category and year before either receiptno is updated, and thus to get the same receipt number.

My next thought was to use sequences, but there are two problems with that. First, it looks like MySQL doesn't have a sequence object. Second, since you would have to have a sequence for each category and year, you'd either have to use dynamic SQL to set which sequence to use (not allowed in triggers), or you'd have to hard-code all the sequence names into your query (complicating the query significantly, and forcing you to update it any time a new category or year is added).

However, we can get around that by using a sequence table. This is a table that has a row for each "sequence" you want, with column(s) that identify the sequences (category and year, in your case), and others that define how the sequence works (starting value, ending value, increment, etc.).

I found an article online that provides the code to set this all up, including some options I hadn't thought of.

To make things best fit your situation, I'd modify their code as follows:

• Instead of naming the sequences, identify them by category and year.
• When using the nextval function, have it automatically create a new sequence if there isn't one for the category and year you pass in.

(Note: the following code is untested, and it's just my tweaks of the code in the article)

CREATE TABLE `sequence_data` (
    `sequence_category` int NOT NULL,
    `sequence_year` int NOT NULL,
    `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
    `sequence_cur_value` bigint(20) unsigned DEFAULT 1,
    `sequence_cycle` boolean NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`seq_category`, `seq_year`)
)


DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    SELECT
        sequence_cur_value INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    IF cur_val IS NOT NULL THEN
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    ELSE
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM sequence_data
                       WHERE sequence_cateogry = seq_category
                       AND   sequence_year = seq_year
                      )
        BEGIN
            INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
            VALUES (seq_category, seq_year, 2);
            SET cur_val = 1;
        END
    END
    END IF;

    RETURN cur_val;
END$$

In your trigger, you'd use this:

SET New.receiptno = nextval(`category`, `year`);

It's important to note that, as written, sequence_cur_val is actually the next value that the sequence will assign, not the last value it did assign. So, when you query sequence_data and see a current value of 71, then the highest assigned value for that category and year is 70. Also, if the current value goes over the maximum value, then it is set to NULL (or, if you change the default setting of sequence_cycle, to the minimum value for the sequence - in your case, you definitely don't want to do that). So, you should probably add a check to see if the function returns NULL, and error out appropriately; otherwise, no receipt number will be set.

Please read the article (and the comments below it) for more details on how this is all meant to work.

Also note that the function must be run inside a transaction, to ensure that you don't get duplicate sequence numbers. If it's only going to be run inside a trigger, then you may not need to explicitly start a transaction inside the function itself.

Actually, I'd be inclined to rearrange the nextval code a little, to make sure we've done our update or insert (which should lock down the value to our session) before I try to set the value. I believe that version would look like this:

DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    IF NOT EXISTS (SELECT 1 FROM sequence_data
                   WHERE sequence_cateogry = seq_category
                   AND   sequence_year = seq_year
                  )
        INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
        VALUES (seq_category, seq_year, 2);
    ELSE
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    END IF;

    SELECT
        IF(sequence_cur_val IS NULL, NULL, sequence_cur_value - sequence_increment) INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    RETURN cur_val;
END$$

(Again, untested code).

Hope this provides help, or at least a direction to consider.