2

MySQL Update/Insert trigger auto-increment over group by

I have a form in which a user can click on a flag (=1 if yes or 0 if not) to create a receipt number (receiptno). I want this to be automated through the database - thus triggers came to mind.

I want to create an auto-increment over a group.

Schema:

 CREATE TABLE booking
    (BuchungId int, `category` int, `year` int, `flag` int, receiptno int)
;

INSERT INTO booking
    (BuchungId,`category`, `year`, `flag`, receiptno)
VALUES
    (1, 1, 2016, 1, NULL),
    (2, 1, 2017, 1, NULL), 
    (3, 1, 2017, 0, NULL), 
    (4, 2, 2017, 1, NULL), 
    (5, 2, 2017, 1, NULL),
    (6, 1, 2017, 1, NULL),
    (7, 1, 2017, 0, NULL),
    (8, 1, 2017, 1, NULL)
;

I have created the body of a trigger:

    CREATE TRIGGER trigger1
        BEFORE UPDATE
        ON booking
        FOR EACH ROW
    BEGIN
      IF (new.Flag = 1) THEN

      SET New.receiptno = COALESCE(old.receiptno,0)+1;


      end IF;

end

Now I would like to update (or insert) the receiptno over a group by on category and year.

Thus eventually resulting in a table like:

(1, 1, 2016, 1, 1),
(2, 1, 2017, 1, 1), 
(3, 1, 2017, 0, NULL), 
(4, 2, 2017, 1, 1), 
(5, 2, 2017, 1, 2),
(6, 1, 2017, 1, 2),
(7, 1, 2017, 0, NULL),
(8, 1, 2017, 1, 3)

http://sqlfiddle.com/#!9/05d7b

  • Note: an "autoincrement" column general refers to a column in a table which is populated by an increasing integer value each time a record is created. IN you sample table, BuchungId might have been defined as an auto_increment column. – RDFozz Jun 19 '17 at 21:27
  • To clarify: receiptno is not a unique receipt number assigned to every booking. instead, receiptno is basically a counter of the number of times the booking has been updated while the flag column is set to "1". So, if booking is updated seven times, the first update sets flag to 1, and no following update sets it to 0, then receiptno would be 7. Is this the intent? Would you maybe need to reset flag to 0 when you increment receiptno to ensure that you're only incrementing it when flag is explicitly set, not when flag is simply unchanged? Where does the group come in? – RDFozz Jun 19 '17 at 21:34
  • Correct. Wrong terminology. Receipt no is not unique inside the table, but inside the desired group (group by category, year). The flag - once set to 0 - would NULL the receipt. I have achieved that part for the least already. What I am looking at is something like "set new.receiptno = max(old.receiptno) over (partition by old.year, oldcategory). Though this wouldn't probably work MSSQL either. – rasenkantenstein Jun 20 '17 at 18:03
  • So: booking rows all start (by default) with receiptno set to NULL. If a user indicates they want a receipt number, flag will be set to 1, and a new receiptno will be generated. And, for all rows where receiptno is not NULL, the combination of category, year, and receiptno must be unique. I assume that a receiptno should only be generated when flag changes from 0 to 1. In other words, if new.flag = 1 AND new.receiptno IS NOT NULL, do nothing. – RDFozz Jun 20 '17 at 18:46
  • Yes. That's my goal. – rasenkantenstein Jun 21 '17 at 7:30
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.