Raccoon February 2016

Trigger MySQL increment if field is already present

I want to increment a field when I insert a new row which is nearly identical than another (the only difference is id_progress). For much clarity I will use this sample

+-------------+---------+---------+---------+
| id_progress | task_id | case_id | loop_nb |
+-------------+---------+---------+---------+
|         800 | 2001002 |     199 | null    |
|         801 | 2001003 |     199 | null    |
|         802 | 2002001 |     199 | null    |
|         803 | 2002002 |     199 | null    |
|         804 | 2001002 |     200 | null    |
|         805 | 2001002 |     199 | 1       |
|         806 | 2001002 |     199 | 2       |
+-------------+---------+---------+---------+

I want to increment the loop_nb of a new row if task_id and case_id are already present in another row.

In this exemple, row 805 was added after row 804. Knowing that 805 is nearly the same than 800, the trigger increment the loop_nb before insert in the database.

I tried something, I am pretty new to trigger so sorry id I did it wrong

BEGIN
 DECLARE loop_nb integer;
 SET @loop_nb := (select loop_nb
     from progress 
     where task_id = NEW.task_id 
     AND case_id = NEW.case_id
     DESC LIMIT 1 );
 IF ISNULL(@loop_nb) THEN
     SET @loop_nb := @loop_nb+1;
 END IF;
 SET NEW.loop_nb = @loop_nb;
 END

But the problem is that my loop_nb is null by default. And I do not think I have the permission to change it.

Is there any alternatives? Should my code be ok if I could change the default values of loop_nb to '0' ?

Answers


wchiquito February 2016

Maybe a code like the following can help, at least, gives you some ideas.

mysql> DELIMITER //

mysql> DROP TRIGGER IF EXISTS `trg_bi_progress`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `progress`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `progress` (
    ->   `id_progress` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `task_id` VARCHAR(7),
    ->   `case_id` INT UNSIGNED,
    ->   `loop_nb` INT UNSIGNED
    -> )//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER `trg_bi_progress` BEFORE INSERT ON `progress`
    -> FOR EACH ROW
    -> BEGIN
    ->   SET NEW.`loop_nb` := (
    ->       SELECT NULLIF(COUNT(`id_progress`), 0)
    ->       FROM `progress`
    ->       WHERE `task_id` = NEW.`task_id`
    ->       AND `case_id` = NEW.`case_id`
    ->     );
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `progress` (`task_id`, `case_id`)
    -> VALUES
    -> ('2001002', 199),
    -> ('2001003', 199),
    -> ('2002001', 199),
    -> ('2002002', 199),
    -> ('2001002', 200),
    -> ('2001002', 199),
    -> ('2001002', 199)//
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> DELIMITER ;

mysql> SELECT
    ->   `id_progress`,
    ->   `task_id`,
    ->   `case_id`,
    ->   `loop_nb`
    -> FROM
    ->   `progress`;
+-------------+---------+---------+---------+
| id_progress | task_id | case_id | loop_nb |
+-------------+---------+---------+---------+
|           1 | 2001002 |     199 |    NULL |
|           2 | 2001003 |     199 |    NULL |
|           3 | 2002001 |     199 |    NULL |
|           4 | 2002002 |     199 |    NULL |
|           5 | 2001002 |     200 |    NULL |
|           6 | 2001002 |     199 |       1 |
|           7 | 2001002 |     199 |       2 |
+-------------+---------+--------- 

Post Status

Asked in February 2016
Viewed 1,340 times
Voted 11
Answered 1 times

Search




Leave an answer