Trigger MySQL increment if field is already present -

i want increment field when insert new row identical (the difference id_progress). clarity use 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 increment loop_nb of new row if task_id , case_id present in row.

in exemple, row 805 added after row 804. knowing 805 same 800, trigger increment loop_nb before insert in database.

i tried something, pretty new trigger sorry id did wrong

begin  declare loop_nb integer;  set @loop_nb := (select loop_nb      progress       task_id = new.task_id       , case_id = new.case_id      desc limit 1 );  if isnull(@loop_nb)      set @loop_nb := @loop_nb+1;  end if;  set new.loop_nb = @loop_nb;  end 

but problem loop_nb null default. , not think have permission change it.

is there alternatives? should code ok if change default values of loop_nb '0' ?

maybe code following can help, @ least, gives 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`     -> each row     -> begin     ->   set new.`loop_nb` := (     ->       select nullif(count(`id_progress`), 0)     ->       `progress`     ->       `task_id` = new.`task_id`     ->       , `case_id` = new.`case_id`     ->     );     -> end// query ok, 0 rows affected (0.00 sec)  mysql> insert `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`     ->     ->   `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 | +-------------+---------+---------+---------+ 7 rows in set (0.00 sec)